Q: Query fails. Error 3464 "data type mismatch in criteria expression." Why this occurs? This thread is locked from future replies

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.


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




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

Did this solve your problem?

Sorry this didn't help.

Question Info

Views: 5,964 Last updated: March 1, 2018 Applies to: