Why doesn't Access quiery work for the year 2021?

Question

I have an access query that I need to pull all the records for a certain month and year. It works fine for all years that are on the list of records except it will not query for the year 2021. Why?

Answer 1

Hum, this should work. I can't think of why 2021 would mess things up.

However, you as a general rule can do this for a query:

SELECT * from tblInvoices where YEAR(InvoiceDate) = 2021  

Or, if you use the query builder, say we have some Tour bookings (From date, To Date) etc.

So, you can filter (query) the table using the query builder if you not into hand building SQL.

So, try something like this:

So the trick is to use YEAR([Your column name goes here]) and then you can of course set the year as per above.

Give above a try - it should work. If it does not, then perhaps the date column is text or some other kind of column type (and NOT a real date/time column).

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Answer 2

that is how I built the query and as I stated it works fine for other prior years. It will just not work for 2021. Makes no sense. If I put 2021 it says type mismatch but 2019,2020 etc. pull up the records just fine.

Answer 3

also, the field is a date field that is from a calculated field in a table. But why would other years pull up and not 2021?

Answer 4

You'll need to post your SQL and any code that might be used. There's probably something going on in code that's affecting the value.

Did you build the database, or did you inherit it? If you inherited it, then there's probably something going on under the hood that you're just not seeing.

Answer 5

I built this. Here is the SQL. I need to turn this into a report I can print monthly. So I have added the Enter Year. I can use the month now to get the current month with no problem, but the year now will not work. So instead I added to enter the year and like I said, it works fine for years prior to 2021.

SELECT [Employees].[Company ID], [Employees].ID, [Employees].[Last Name], [Employees].[First Name], [Employees].DOH, [Employees].[Eligibility Date], [Employees].Age, Month([Eligibility Date]) AS [Month], Year([Eligibility Date]) AS [Year]
FROM [Employees]
WHERE (((Month([Eligibility Date]))=Month(Now())) AND ((Year([Eligibility Date]))=[Enter Year]) AND (([Employees].[Employee Status - Current])="A"));