Baffled

[TheSearcher]

When I run this query the proper results are returned:
SELECT tbl_Authorizations.Client_LName, tbl_Authorizations.Client_Id, tbl_Authorizations.Auth_No, tbl_Authorizations.Auth_EndDate
FROM tbl_Authorizations
ORDER BY tbl_Authorizations.Client_LName;

Results:
Client_LName Client_Id Auth_No Auth_EndDate
Gibson 600001 3xxxxxxx 1/31/2022
Gibson 600001 2xxxxxxxx 1/31/2022
Gibson 600001 1xxxxxxx 1/31/2022
Pacino 600002 3zzzzzz 1/31/2022
Pacino 600002 2zzzzzz 1/31/2022
Pacino 600002 1zzzzzz 1/31/2022

However when I include Date() in the Where clause nothing is returned. Why? Today is 1/20/2022.

SELECT tbl_Authorizations.Client_LName, tbl_Authorizations.Client_Id, tbl_Authorizations.Auth_No, tbl_Authorizations.Auth_EndDate
FROM tbl_Authorizations
WHERE (((tbl_Authorizations.Auth_EndDate)<=Date()))
ORDER BY tbl_Authorizations.Client_LName;

Thanks in advance,
TS

 

[plog]

My guess is that Auth_EndDate isn't a date. Is it? Or is it stored as a Short Text in the table?

 

[TheSearcher]

Auth_EndDate is a date field. It is stored as Short Date but I also tried General Date.
I also tried <= #1/31/2022#.
Still returns nothing.

 

[plog]

Can you post a sample of the database?

 

[TheSearcher]

see attached. Thanks for your help.

 

[MarkK]

None of the dates in the table satisfy the condition. If I edit Auth_EndDate to be <= today, then it appears in the query.

 

[TheSearcher]

I need a vacation. Thank you.

 

[jdraw]

None of the Auth_enddate are < or = to Jan 20 (today)

This works
SELECT tbl_Authorizations.Client_LName, tbl_Authorizations.Client_Id, tbl_Authorizations.Auth_No, tbl_Authorizations.Auth_EndDate
FROM tbl_Authorizations
WHERE (((tbl_Authorizations.Auth_EndDate)>Date()))
ORDER BY tbl_Authorizations.Client_LName;