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

 

 
Question Info

Last updated July 2, 2018 Views 6,157 Applies to:
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))

---
Kind regards, HansV
www.eileenslounge.com

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

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

How satisfied are you with this response?

Thanks for your feedback.