Build whre statement

[David McKnight]

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 & "));"

 

[Joel]

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.

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 & "));"