This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(163.2, 36%, 25%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EK%3C/text%3E%3C/svg%3E)
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?
Above is the SQL using the Eligibility Date field (a calculated field in a table) Just in case this type of field was causing the problem in some way, I changed the query to not use that field and created a field in the query to give me the same eligibility date and tried using it that way. Same thing. Works for years prior to 2021 but not 2021. I'm so perplexed. Here is that SQL.
SELECT [Employees].[Company ID], [Employees].ID, [Employees].[Last Name], [Employees].[First Name], [Employees].DOH, DateSerial(Year([DOH]),Month([DOH]),Day([DOH])+90) AS [Eligibility Date], [Employees].Age, Month([Eligibility Date]) AS [Month], DatePart("yyyy",[Eligibility Date]) AS [Year]
FROM [Employees]
WHERE (((Month([Eligibility Date]))=Month(Now())) AND ((DatePart("yyyy",[Eligibility Date]))=[Enter Year]) AND (([Employees].[Employee Status - Current])="A"));
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(262.40000000000003, 46%, 35%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAK%3C/text%3E%3C/svg%3E)
is this a access table or a linked table - say to sql server?
Also, if you do a debug-compile (inside the VBA editor - does the code compile?
There must be some pesky detail that is at play here. I would try the VBA editor - > debug->compile.
Any errors in the VBA code could effect and result in a broken reference.
Also, since month, year etc. are functions and reserved words? I would try using MyMonth, MyYear etc. for the alias columns - Access is going to struggle that if the resulting column name is a year function, or the actual year!!!
Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
I really appreciate at all the attempts to help! It is just a table. Not linked.
I tried renaming it to ThisYear and it makes no difference. I am a novice as far as actual programming code goes however, I have been using Access for 12 years successfully and have never had this type of issue. I tried to run the debugger as suggested and only saw a grey screen. I saw nothing happening. Not sure what that means.
Hum ok - that helps! - I would consider using MyMonth, Myyear as the aliased columns. The problem is Access seems to be confused between Month() as a function, and month as a column!!!! - they both have the exact same name - so which one access might decide to choose (the function or your column with the same name) is a source of confusing to Access.
Of course, if you change those names, then say your reports or say some query or export might have to be changed. If this is just one or two reports, then I would make this change. (try to stop using month and year as columns - as Access is going to struggle as to if this means a column name month, or a function called month. And with a same name, then you can get those so called "circular" referances that Excel often complains about.
R
Albert
I've already tried that. It has nothing to do with the name. In looking for help online, I have written this multiple ways, and each way works perfectly with all years prior to 2021. I even tried to make the eligibility date to 2022 to see if that works and it does not. No year beyond 2020 works. ALL years prior to 2020 work. This is unbelievably perplexing.
To all that tried to help, thank you! I have finally resolved the issue. In my search for a solution, I found an answer to someone's old question in 2011 that was similar but different, yet the problem was the same.
I had 2 eligibility date fields that were blank in the whole table. I don't know why this didn't effect years prior to 2021, but adding the dates resolved the issue.
Again. thanks