Ask a new question

Query fails. Error 3464 "data type mismatch in criteria expression." Why this occurs?

Hello everybody

The query below, made in ACCESS 2007, generates Error 3464 "data type mismatch in criteria expression." Why this occurs?

Note also that this query was already tested converting, via CDate, both the calculated field and the criteria expression, but still generated the same error.


SELECT

tblE15.[CCF], DateSerial(Left([tblE14.DateReverse],4),Mid([tblE14. DateReverse],5,2),Right([tblE14. DateReverse],2)) AS DateNormal, tblE15.Description

FROM tblE14 RIGHT JOIN tblE15 ON tblE14.[CCF] = tblE15.[CCF]

WHERE DateSerial (Left([tblE14.DateReverse],4), Mid([tblE14. DateReverse],5,2), Right([tblE14. DateReverse],2)) =[Forms]![frmInfoUser]![txbStartDate] AND (Not (tblE14.DateReverse) Is Null);


Important details:

tblE14.DateReverse is a Text type field; their values are texts in standard YYYYMMDD (for instance: 20081230);

DateNormal is a calculated field alias that returns data in the standard DD/MM/YYYY via DateSeriral function;

Forms]![frmInfoUser]![txbStartDate] is bounded to the StartDate date type field that belongs to tblInfoUser table;


Thanks in advance

 

Answer
Answer

You could try using

 

DateSerial(Left(Nz([tblE14].[DateReverse], "19000101"), 4), Mid(Nz([tblE14].[DateReverse], "19000101"), 5, 2), Right(Nz([tblE14].[DateReverse], "19000101"), 2))

---
Best wishes, HansV
https://www.eileenslounge.com

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated August 3, 2022 Views 7,283 Applies to: