How do I use LIKE with a date field in MS Access with a NULL value

Question

I have an application that allows partial date searches. It doesn't specify what the date part is. if someone puts in "20", it could be part of the year or a day of the month.

The application uses an Access databases where some of the date fields have NULL values.

I am trying to write a query with [date field] LIKE {date criteria}.

What I started to do is convert the date field to a string using cstr(datefield), but the NULL values are giving #error values.

SELECT * ,cstr(datefield) AS strdate FROM table1

Access has the ISNULL function, but it only takes one parameter which returns true or false, as opposed to the ISNULL function in SQL Server, which takes two parameters, the second being the value to use if the first parameter is null.

I supposed I could add "AND datefield IS NOT NULL" to the end, but I wanted to ask if there were any other possible solutions to this.

Then when I did finally get to the query itself, it didn't return any results.

SELECT * ,cstr(datefield) AS strdate 
FROM table1 
WHERE scan_date IS NOT NULL
AND cstr(datefield) LIKE '%20%'

How would I do a LIKE query against a date field in access?

Most people here want sample table data and the expected result as formatted text, not as images. — jarlh
Sample table data would just be random dates, which are not difficult to imagine. I didn't show any expected results, since it would be the same as the sample table data, just without the errors. The image shows the errors. Nothing special about the data for this question. Just dates. — RickInWestPalmBeach

forpas forpas · Accepted Answer · 2020-04-10T19:28:36

You can use the function Nz():

SELECT * , Nz(datefield) AS strdate 
FROM table
WHERE Nz(datefield) LIKE '*20*'

But you should know that the wildcard operator to use in Access is not % but * and this is the reason that you don't get any results.

How do I use LIKE with a date field in MS Access with a NULL value

1 Answers