MS ACCESS: Show records that display only current month and previous two months

Hello Team

Can you please advise on the following.

I have an MS Access query which contains the following fields:

GroupID, GroupName, StartDate, EndDate, CompleteDate. Fields are pulled from a table called tblGroupBusiness.

What is required is to create a parameter of within the query which will display only three months at one time. For example:

As we are currently in the month of March, the query would only display records that have a CompleteDate from 01/01/19 through to 31/03/19.  Once we move into the month of April, all records that have a CompleteDate within the month of Jan will be hidden and the recordset would then only display all records from Feb to the end of April.

As this result can be achieved using various methods, e.g., SQL, VBA, Access functions directly into the Criteria line etc, I am happy to receive suggestions in a varied way.

Thank you in advance.

Dale
Dale JamesTherapistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul Cook-GilesSenior Application DeveloperCommented:
Use the DateSerial function to calculate the first day of the month that was two months ago: DateSerial(Year(Date()), Month(Date())-2, 1), and the last day of the current month:  DateSerial(Year(Date()), Month(Date())+1, 0).

You'll need to concatenate the date delimiter # with the functions:

 where CompleteDate between #" & DateSerial(Year(Date()), Month(Date())-2, 1) & "# and #" DateSerial(Year(Date()), Month(Date())+1, 0) & "#".

I note that you're using (what I think is the) European date format (31/03/19) where an American would say 3/31/19.  I don't think that will affect the way DateSerial works, but definitely test and let us know.  :)
Dale JamesTherapistAuthor Commented:
Hello Paul

Thank you for your response and for providing the above syntax.

Yes you correct, I am using the Euro date format within this particular query.

Will apply this to the query and let you know how I get on.

Thanks again.

Dale
John TsioumprisSoftware & Systems EngineerCommented:
SELECT GroupID, GroupName, StartDate, EndDate, CompleteDate FROM tblGroupBusiness WHERE Month(CompleteDate) between Month(DateAdd("m",-2,date())) AND Month(Date)

Open in new window

CEOs need to know what they should worry about

Nearly every week during the past few years has featured a headline about the latest data breach, malware attack, ransomware demand, or unrecoverable corporate data loss. Those stories are frequently followed by news that the CEOs at those companies were forced to resign.

Gustav BrockCIOCommented:
It is as simple as this - which uses true Date values  not sensitive to localised formats:

where CompleteDate between DateSerial(Year(Date()), Month(Date())-2, 1) and DateSerial(Year(Date()), Month(Date())+1, 0)"

Open in new window

Ryan ChongSoftware Team LeadCommented:
for John's suggestion above, we need to consider the Year comparison as well, in the case we don't expect to keep one year of data only : )

in addition, we need to know if data field: CompleteDate do keep the time as well? if yes, the where clause need to be adjusted accordingly.
John TsioumprisSoftware & Systems EngineerCommented:
Good catch Ryan..
SELECT GroupID, GroupName, StartDate, EndDate, CompleteDate FROM tblGroupBusiness WHERE (Month(CompleteDate) BETWEEN  Month(DateAdd("m",-2,date()))) AND Year(CompleteDate) BETWEEN Year(DateAdd("m",-2,date())) AND Year(Date))
Dale JamesTherapistAuthor Commented:
Hello Folks....

just want to thank everyone for their response to my original question and to confirm that all worked perfectly.

Thanks again.

Sincerely

Dale
Gustav BrockCIOCommented:
You are welcome!
Then please mark as answered.

Have a nice weekend.
John TsioumprisSoftware & Systems EngineerCommented:
Well for some bizzare reason when i tried to edit my 1st post the code tags simply wouldn't work...but it seems but somehow this was for a reason
So here is my edited query to correctly take in account the case of dates in 2 different years (e.g. 2018 , 2019)
SELECT GroupID, GroupName, StartDate, EndDate, CompleteDate FROM tblGroupBusiness  
 WHERE  (iif(Month(DateAdd("m",-4,Date())) > Month(Date()),((Month([CompleteDate]) BETWEEN Month(DateAdd("m",-4,Date()))  AND 12) AND (YEAR(Todays)=Year(date())-1 )) AND ((Month(Todays) BETWEEN 1  AND Month(date())) AND (YEAR([CompleteDate])=Year([CompleteDate ]) )) ,Month([Todays]) BETWEEN Month([CompleteDate ])  AND Month(DATE() )) )

Open in new window

I raised the dif to 4 months to make it easier to spot...and also added an attached db for showing
Database141.accdb

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale JamesTherapistAuthor Commented:
Hello John

Thank you so much for your extended post...very much appreciated!!

I placed this one in to the query and it worked perfectly.

Thank you once again.

Sincerely

Dale
Dale JamesTherapistAuthor Commented:
Once again....thank you to all of you for the time and effort provided to answer my initial question.

Sincerely

Dale
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.