Data type mismatch in criteria expression

[Thorson]

I have several queries that have been working perfectly, however I recently
added a field "EntryDate" and a criteria expression to limit which records
are pulled up. Now the queries will sometimes run and look fine, but if I
try to sort or look through the records the error "Data type mismatch in
criteria expression" comes up, this often comes up when I initially run the
query as well. I'm assuming something is wrong with the Criteria/Where part
of my SQL:

SELECT [qryBirthInfoandPurchas/Don].eartag,
[qryBirthInfoandPurchas/Don].calftattoo, tblBirthInformation.EntryDate,
[qryBirthInfoandPurchas/Don].calfbirthdate, tblBirthInformation.EntryDate,
[qryBirthInfoandPurchas/Don].calfsex, [qryBirthInfoandPurchas/Don].Expr1004,
[qryBirthInfoandPurchas/Don].BirthUnit,
[qryBirthInfoandPurchas/Don].BirthLocation
FROM tblBirthInformation RIGHT JOIN ([qryBirthInfoandPurchas/Don] RIGHT JOIN
[qryCurrentInventoryBirthInfoandPurchas/Don] ON
[qryBirthInfoandPurchas/Don].eartag =
[qryCurrentInventoryBirthInfoandPurchas/Don].eartag) ON
tblBirthInformation.EarTag =
[qryCurrentInventoryBirthInfoandPurchas/Don].eartag
WHERE
(((tblBirthInformation.EntryDate)<=DateSerial(Year([qryBirthInfoandPurchas/Don]![calfbirthdate]),Month([qryBirthInfoandPurchas/Don]![calfbirthdate])+1,5))
AND
(([qryBirthInfoandPurchas/Don].calfbirthdate)<=[Forms]![frmCurrentInventoryDateQuery]![txtDate]))
OR
(((tblBirthInformation.EntryDate)>DateSerial(Year([qryBirthInfoandPurchas/Don]![calfbirthdate]),Month([qryBirthInfoandPurchas/Don]![calfbirthdate])+1,5))
AND
((tblBirthInformation.EntryDate)<=[Forms]![frmCurrentInventoryDateQuery]![txtDate]));


Thanks for any help you can provide!

 

[Jeff Boyce]

Thorson

I believe your WHERE clause needs to delimit the date values with the date
delimiters (i.e., "#" -- for example, #7/1/2009#).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jerry Whittle]

What data type is EntryDate at the table level? Also would you happen to be
an a country that uses D/M/Y format instead of the US M/D/Y?

 

[KARL DEWEY]

You have tblBirthInformation.EntryDate in the select twice.

Verify that tblBirthInformation.EntryDate is a DateTime datatype.

Try changing like this --
WHERE
(((tblBirthInformation.EntryDate)<=DateSerial(Year([qryBirthInfoandPurchas/Don].[calfbirthdate]),Month([qryBirthInfoandPurchas/Don].[calfbirthdate])+1,5))
AND
(([qryBirthInfoandPurchas/Don].calfbirthdate)<=CVDate([Forms]![frmCurrentInventoryDateQuery]![txtDate])))
OR
(((tblBirthInformation.EntryDate)>DateSerial(Year([qryBirthInfoandPurchas/Don].[calfbirthdate]),Month([qryBirthInfoandPurchas/Don].[calfbirthdate])+1,5))
AND
((tblBirthInformation.EntryDate)<=CVDate([Forms]![frmCurrentInventoryDateQuery]![txtDate])));

 

[Thorson]

Yes, the two tblBirthInformation.EntryDate have different criteria, however
when I save the query Access will group the criteria into one cell, so I am
assuming I actually can just leave it like that and delete the second
tblBirthInformation.EntryDate

tblBirthInformation.EntryDate is a Date/Time datatype

tblBirthInformation.Calf BirthDate is also a Date/Time datatype

I tried changing the Where statement with what you suggested but I still
have the error "Data type mismatch in the criteria expression"

 

[Thorson]

See the reply below:
tblBirthInformation.EntryDate and tblBirthInformation.CalfBirthDate are both
Date/Time Datatype.

I am in the U.S. and do use the date format m/d/y


--
Thorson

What data type is EntryDate at the table level? Also would you happen to be
an a country that uses D/M/Y format instead of the US M/D/Y?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have several queries that have been working perfectly, however I recently
added a field "EntryDate" and a criteria expression to limit which records
are pulled up. Now the queries will sometimes run and look fine, but if I
try to sort or look through the records the error "Data type mismatch in
criteria expression" comes up, this often comes up when I initially run the
query as well. I'm assuming something is wrong with the Criteria/Where part
of my SQL:

SELECT [qryBirthInfoandPurchas/Don].eartag,
[qryBirthInfoandPurchas/Don].calftattoo, tblBirthInformation.EntryDate,
[qryBirthInfoandPurchas/Don].calfbirthdate, tblBirthInformation.EntryDate,
[qryBirthInfoandPurchas/Don].calfsex, [qryBirthInfoandPurchas/Don].Expr1004,
[qryBirthInfoandPurchas/Don].BirthUnit,
[qryBirthInfoandPurchas/Don].BirthLocation
FROM tblBirthInformation RIGHT JOIN ([qryBirthInfoandPurchas/Don] RIGHT JOIN
[qryCurrentInventoryBirthInfoandPurchas/Don] ON
[qryBirthInfoandPurchas/Don].eartag =
[qryCurrentInventoryBirthInfoandPurchas/Don].eartag) ON
tblBirthInformation.EarTag =
[qryCurrentInventoryBirthInfoandPurchas/Don].eartag
WHERE
(((tblBirthInformation.EntryDate)<=DateSerial(Year([qryBirthInfoandPurchas/Don]![calfbirthdate]),Month([qryBirthInfoandPurchas/Don]![calfbirthdate])+1,5))
AND
(([qryBirthInfoandPurchas/Don].calfbirthdate)<=[Forms]![frmCurrentInventoryDateQuery]![txtDate]))
OR
(((tblBirthInformation.EntryDate)>DateSerial(Year([qryBirthInfoandPurchas/Don]![calfbirthdate]),Month([qryBirthInfoandPurchas/Don]![calfbirthdate])+1,5))
AND
((tblBirthInformation.EntryDate)<=[Forms]![frmCurrentInventoryDateQuery]![txtDate]));


Thanks for any help you can provide!

 

[John Spencer]

I strongly suspect that the query
[qryBirthInfoandPurchas/Don]
is returning values for [calfbirthdate] that cannot be interpreted as a date.
This then causes an error

For instance:
IF [calfbirthdate] is NULL then Month([calfbirthdate]) returns NULL and then
DateSerial will error with Invalid use of Null error. Which in a query will
probably give you a Data Type Mismatch error.

So you need to go back and check [qryBirthInfoandPurchas/Don] and what it is
generating for CalfBirthDate

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have several queries that have been working perfectly, however I recently
added a field "EntryDate" and a criteria expression to limit which records
are pulled up. Now the queries will sometimes run and look fine, but if I
try to sort or look through the records the error "Data type mismatch in
criteria expression" comes up, this often comes up when I initially run the
query as well. I'm assuming something is wrong with the Criteria/Where part
of my SQL:

SELECT [qryBirthInfoandPurchas/Don].eartag,
[qryBirthInfoandPurchas/Don].calftattoo, tblBirthInformation.EntryDate,
[qryBirthInfoandPurchas/Don].calfbirthdate, tblBirthInformation.EntryDate,
[qryBirthInfoandPurchas/Don].calfsex, [qryBirthInfoandPurchas/Don].Expr1004,
[qryBirthInfoandPurchas/Don].BirthUnit,
[qryBirthInfoandPurchas/Don].BirthLocation
FROM tblBirthInformation RIGHT JOIN ([qryBirthInfoandPurchas/Don] RIGHT JOIN
[qryCurrentInventoryBirthInfoandPurchas/Don] ON
[qryBirthInfoandPurchas/Don].eartag =
[qryCurrentInventoryBirthInfoandPurchas/Don].eartag) ON
tblBirthInformation.EarTag =
[qryCurrentInventoryBirthInfoandPurchas/Don].eartag
WHERE
(((tblBirthInformation.EntryDate)<= DateSerial(Year([qryBirthInfoandPurchas/Don]![calfbirthdate]),Month([qryBirthInfoandPurchas/Don]![calfbirthdate])+1,5))

AND
(([qryBirthInfoandPurchas/Don].calfbirthdate)<=[Forms]![frmCurrentInventoryDateQuery]![txtDate]))
OR
(((tblBirthInformation.EntryDate)>

DateSerial(Year([qryBirthInfoandPurchas/Don]![calfbirthdate]),Month([qryBirthInfoandPurchas/Don]![calfbirthdate])+1,5))

AND
((tblBirthInformation.EntryDate)<=[Forms]![frmCurrentInventoryDateQuery]![txtDate]));


Thanks for any help you can provide!

 

[Thorson]

I went and looked, that was the problem. Thank you, I'm glad it was
something simple!

Thanks for your help.