Expression does not match criteria error

D

David McKnight

I get a " Data type mismatch in criteria expression" error with the where
statement in my query build code.

It works fine when txtSeason is equal to txtEndSeason such as 1880 & 1880,
but I get this error when I a range such as 1880 and 1889.

Whole query is:


"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And (Scores.Season)<="
& Me.txtEndSeason.Value & ") AND ((Format([Scores].[Date],'ww'))<= " &
Me.cmbWeekNo.Value & "));"


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

OldPro

I get a " Data type mismatch in criteria expression" error with the where
statement in my query build code.

It works fine when txtSeason is equal to txtEndSeason such as 1880 & 1880,
but I get this error when I a range such as 1880 and 1889.

Whole query is:

"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And (Scores.Season)<="
& Me.txtEndSeason.Value & ") AND ((Format([Scores].[Date],'ww'))<= " &
Me.cmbWeekNo.Value & "));"

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 & "));"
Click to expand...

What type is Scores.[Season]?
 
D

David McKnight

Long Integer
--
David McKnight


OldPro said:
I get a " Data type mismatch in criteria expression" error with the where
statement in my query build code.

It works fine when txtSeason is equal to txtEndSeason such as 1880 & 1880,
but I get this error when I a range such as 1880 and 1889.

Whole query is:

"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And (Scores.Season)<="
& Me.txtEndSeason.Value & ") AND ((Format([Scores].[Date],'ww'))<= " &
Me.cmbWeekNo.Value & "));"

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 & "));"
Click to expand...

What type is Scores.[Season]?
Click to expand...
 
D

David McKnight

Q:What type is Scores.[Season]?

A: I believe what your asking is what type data is this field, it so it is
format as a long interger. Do I need to format both Me.txtEndSeason.Value &
Me.txtSeason.Value as a long interger in the code?
--
David McKnight


David McKnight said:
Long Integer
--
David McKnight


OldPro said:
I get a " Data type mismatch in criteria expression" error with the where
statement in my query build code.

It works fine when txtSeason is equal to txtEndSeason such as 1880 & 1880,
but I get this error when I a range such as 1880 and 1889.

Whole query is:

"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And (Scores.Season)<="
& Me.txtEndSeason.Value & ") AND ((Format([Scores].[Date],'ww'))<= " &
Me.cmbWeekNo.Value & "));"

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 & "));"
Click to expand...

What type is Scores.[Season]?
Click to expand...
Click to expand...
 
D

David McKnight

I figured out my own question. I believe problem was that I had some Dates
blank and so Week could not be calculated. I will run my query later to make
sure. Sorry if anyone invested any time trying to figure this one out.
--
David McKnight


David McKnight said:
Q:What type is Scores.[Season]?

A: I believe what your asking is what type data is this field, it so it is
format as a long interger. Do I need to format both Me.txtEndSeason.Value &
Me.txtSeason.Value as a long interger in the code?
--
David McKnight


David McKnight said:
Long Integer
--
David McKnight


OldPro said:
On Sep 16, 11:42 am, David McKnight
I get a " Data type mismatch in criteria expression" error with the where
statement in my query build code.

It works fine when txtSeason is equal to txtEndSeason such as 1880 & 1880,
but I get this error when I a range such as 1880 and 1889.

Whole query is:

"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And (Scores.Season)<="
& Me.txtEndSeason.Value & ") AND ((Format([Scores].[Date],'ww'))<= " &
Me.cmbWeekNo.Value & "));"

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 & "));"
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Build whre statement 1
Type mismatch error after converting to Access 2007 2
Inner , Right, Left JOIN 15
selecting two query senerios with code 2
Dynamic Range and arrays 1
lots of extra information and no attachmnet 1

Top