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 |
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 |
All times are GMT +1. The time now is 10:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com