Ms Access Date function not passing in LIKE query

I want to query records from the access database that matches the current date (system date)

The following query works fine if I enter the date literally.

SELECT SUBJECT, TYPE, CH_NO, TEST_NAME, TEST_CODE, SDATE, ID
FROM MCQS_PAPER
WHERE [SDATE] ALIKE "%5/14/2022%"

I have added the Date() function in the criteria to avoid manual work, but it’s not returning any records.

SELECT SUBJECT, TYPE, CH_NO, TEST_NAME, TEST_CODE, SDATE, ID
FROM MCQS_PAPER
WHERE [SDATE] ALIKE "% Date() %"

Why does the date function not work?

>Solution :

Comparing a date/time value with a string is wrong. You can test a date/time value (e.g. 05/14/2022 11:15 AM) against current date (e.g. 05/14/2022) like so:

WHERE [SDATE] >= Date()
AND   [SDATE] < DateAdd("d", 1, Date())

Leave a Reply