Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Build whre statement
Im having problems with the statement following in my code:
"WHERE (((Scores.Season)= " & Me.txtSeason.Value & " And (Scores.Season)<=" & Me.txtEndSeason.Value & ") AND ((Format([Scores].[Date],'ww'))<= " & Me.cmbWeekNo.Value & "));" It works fine if txtEndSeason.Value is equal to txtSeason.Value but if I have a range say 1880 and 1889 respectively, I get an error stating "...data type mismatch in criteria expression.." when I run the code. Full build query statement: qdf.SQL = "SELECT Scores.ID, Scores.Date, Scores.Season, Scores.Visitor, Scores.[Visitor Score], Scores.Home, Scores.[Home Score], Scores.Margin, Scores.Nuetral, Scores.Location, Scores.Location2, Scores.Line," & _ "IIf(Scores!Date-(Now()-0)0,1,0) AS [Future Game], IIf(Scores!Margin=0,0,((Scores!Margin)/(Abs(Scores!Margin)))) AS [Win-Loss], IIf(Round(((((Scores!Date-12/31/1900)/365.25)-(Int((Scores!Date-12/31/1900)/365.25)))*12),1)9.3,1," & _ "IIf(Round(((((Scores!Date-12/31/1900)/365.25)-(Int((Scores!Date-12/31/1900)/365.25)))*12),1)<1,1,-1)) AS [September Game], IIf(Round(((((Scores!Date-12/31/1900)/365.25)-(Int((Scores!Date-12/31/1900)/365.25)))*12),1)11.2,1," & _ "IIf(Round(((((Scores!Date-12/31/1900)/365.25)-(Int((Scores!Date-12/31/1900)/365.25)))*12),1)<1,1,-1)) AS [Bowl Game], 1+(([Home Score]+10)/([Home Score]+[Visitor Score]+20)) AS [Percent Margin]," & _ "IIf(Abs([Margin])=0,0,(Log(Abs([Margin])))*([Margin]/(Abs([Margin])))) AS [Adj Log Margin]," & _ "IIf(Scores!Nuetral=0,Scores!Margin-Coefficients![Home Field Adv Margin],Scores!Margin) AS [Adjusted Margin], IIf(Scores!Nuetral=0,[Win-Loss]-Coefficients![Home Field AdvWin-Loss],[Win-Loss])" & _ "AS [Adjusted Margin Win-Loss], [School Season Division].Division," & _ "[School Season Division_1].Division, [Date] & RTrim(Scores!Home) & RTrim(Scores!Visitor) AS Game, Format(Scores.Date,'ww') AS Week " & _ "FROM Coefficients, [School Season Division] INNER JOIN ([School Season Division] AS [School Season Division_1] " & _ "INNER JOIN Scores ON ([School Season Division_1].Season = Scores.Season) AND ([School Season Division_1].Home = Scores.Home)) " & _ "ON ([School Season Division].Home = Scores.Visitor) AND ([School Season Division].Season = Scores.Season)" & _ "WHERE (((Scores.Season)= " & Me.txtSeason.Value & " And (Scores.Season)<=" & Me.txtEndSeason.Value & ") AND ((Format([Scores].[Date],'ww'))<= " & Me.cmbWeekNo.Value & "));" -- David McKnight |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Build whre statement
It looks like you are comparing text strings not numbers. for number use
(greater) and < (less) for strings use Strcomp Returns a Variant (Integer) indicating the result of a string comparison. Syntax StrComp(string1, string2[, compare]) The StrComp function syntax has these named arguments: Part Description string1 Required. Any valid string expression. string2 Required. Any valid string expression. compare Optional. Specifies the type of string comparison. If the compare argument is Null, an error occurs. If compare is omitted, the Option Compare setting determines the type of comparison. "David McKnight" wrote: Im having problems with the statement following in my code: "WHERE (((Scores.Season)= " & Me.txtSeason.Value & " And (Scores.Season)<=" & Me.txtEndSeason.Value & ") AND ((Format([Scores].[Date],'ww'))<= " & Me.cmbWeekNo.Value & "));" It works fine if txtEndSeason.Value is equal to txtSeason.Value but if I have a range say 1880 and 1889 respectively, I get an error stating "...data type mismatch in criteria expression.." when I run the code. Full build query statement: qdf.SQL = "SELECT Scores.ID, Scores.Date, Scores.Season, Scores.Visitor, Scores.[Visitor Score], Scores.Home, Scores.[Home Score], Scores.Margin, Scores.Nuetral, Scores.Location, Scores.Location2, Scores.Line," & _ "IIf(Scores!Date-(Now()-0)0,1,0) AS [Future Game], IIf(Scores!Margin=0,0,((Scores!Margin)/(Abs(Scores!Margin)))) AS [Win-Loss], IIf(Round(((((Scores!Date-12/31/1900)/365.25)-(Int((Scores!Date-12/31/1900)/365.25)))*12),1)9.3,1," & _ "IIf(Round(((((Scores!Date-12/31/1900)/365.25)-(Int((Scores!Date-12/31/1900)/365.25)))*12),1)<1,1,-1)) AS [September Game], IIf(Round(((((Scores!Date-12/31/1900)/365.25)-(Int((Scores!Date-12/31/1900)/365.25)))*12),1)11.2,1," & _ "IIf(Round(((((Scores!Date-12/31/1900)/365.25)-(Int((Scores!Date-12/31/1900)/365.25)))*12),1)<1,1,-1)) AS [Bowl Game], 1+(([Home Score]+10)/([Home Score]+[Visitor Score]+20)) AS [Percent Margin]," & _ "IIf(Abs([Margin])=0,0,(Log(Abs([Margin])))*([Margin]/(Abs([Margin])))) AS [Adj Log Margin]," & _ "IIf(Scores!Nuetral=0,Scores!Margin-Coefficients![Home Field Adv Margin],Scores!Margin) AS [Adjusted Margin], IIf(Scores!Nuetral=0,[Win-Loss]-Coefficients![Home Field AdvWin-Loss],[Win-Loss])" & _ "AS [Adjusted Margin Win-Loss], [School Season Division].Division," & _ "[School Season Division_1].Division, [Date] & RTrim(Scores!Home) & RTrim(Scores!Visitor) AS Game, Format(Scores.Date,'ww') AS Week " & _ "FROM Coefficients, [School Season Division] INNER JOIN ([School Season Division] AS [School Season Division_1] " & _ "INNER JOIN Scores ON ([School Season Division_1].Season = Scores.Season) AND ([School Season Division_1].Home = Scores.Home)) " & _ "ON ([School Season Division].Home = Scores.Visitor) AND ([School Season Division].Season = Scores.Season)" & _ "WHERE (((Scores.Season)= " & Me.txtSeason.Value & " And (Scores.Season)<=" & Me.txtEndSeason.Value & ") AND ((Format([Scores].[Date],'ww'))<= " & Me.cmbWeekNo.Value & "));" -- David McKnight |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to build a If statement | Excel Worksheet Functions | |||
How to build effective IF Statement for recurring value in cells....sensitivity analysis | Excel Discussion (Misc queries) | |||
Trying to build a formula | Excel Worksheet Functions | |||
How to build build a macro that automatically imports | Excel Programming | |||
Build Add-in in C++ | Excel Programming |