CODEWITHSUNDEEP
sqlms-access
hisoka
hisoka

Reputation: 354

MS Access Query - Incompatible Types (Dates)

I am trying to get a range of records within the last 5 years. Unfortunately, the data type on my column is a string with a slightly odd formatting. For example, December 2018 would be represented as : 2018/012.

I have tried making the following query :

SELECT tbl.StrDate
FROM tbl
WHERE DateValue(StrDate) > DateAdd("yyyy", -5, Date());

The query works for a fraction of a second (as in I see the results)... However, it pops up a display saying that there are "incompatible types within the query criterias" and the entire column changes to #Name ?.

I am not quite sure what is causing this issue. Thanks for the help.

Note: I am using a French version of Access 2010 the wording might be slightly different here.

Upvotes: 0

Views: 66

Answers (2)

Gustav
Gustav

Reputation: 55806

Try ignoring the invalid "dates":

SELECT tbl.StrDate
FROM tbl
WHERE DateValue(IIf(IsDate(StrDate), strDate, Date()) > DateAdd("yyyy", -5, Date())

or list - and correct - those "dates":

SELECT tbl.StrDate
FROM tbl
WHERE NOT IsDate(StrDate);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you are willing to ignore the day portion, then use left() and val():

WHERE VAL(LEFT(StrDate, 4)) > DateAdd("yyyy", -5, Date());

Actually, LEFT() isn't needed, but I prefer to keep it for clarity.

Upvotes: 0

Related Questions