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
Similar Threads
