Recognition of a date as a date in Access Forms filtering

user_19801288 0
Viewed 32

I have an Access data table with the Field "RequiredUntil", which is from the type "Date/Time", and several other columns, and a form, in which I show that table, and where I usually use right mouse click to filter for what I want. The text box for "RequiredUntil" is formatted as "Short Date".

Part of the form:

Picture of part of the form

When I now do a right click on the field "RequiredUntil" in that form, Access properly recognizes that as a date, and offers me several date filters, i.e. Today, "This week", Past,... So I don't use any SQL there, all is done by Access!

When I select "Today" (or Yesterday or Tomorrow), I get a "Data type mismatch in criteria expression" error. When I select "This week" (or any other time period), the filter is properly set - but as soon as I filter the next column, I get a

    Syntax Error in Query expression '((Year([DataTable].[RequiredUntil])=Year(Date())) AND 
    (DatePart("ww";[DataTable].[RequiredUntil];0)=DatePart("ww",Date(),0))'.

In that case, the date filter is revoked, and only the second filter is applied.

So, I don't do any SQL, RequiredUntil is of the type Date/Time, the text field in the Form is Date/Time, Access recognizes it as Date - what am I missing?

type-mismatch ms-access filter
1 Answers

I'm pretty sure, the issue is caused by Null values in your date field.

If removed, you can set the filter, which will reveal as:

(DateSerial(Year([DataTable].[RequiredUntil]), Month([DataTable].[RequiredUntil]), Day([DataTable].[RequiredUntil])) = Date()+1)

which, obviously, will fail, as DateSerial doesn't accept Null values.

user_35272970
Related