We help IT Professionals succeed at work.

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

85 Views
Last Modified: 2019-03-24
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
Comment
Watch Question

Paul Cook-GilesSenior Application Developer
CERTIFIED EXPERT

Commented:
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 JamesTherapist

Author

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 Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
SELECT GroupID, GroupName, StartDate, EndDate, CompleteDate FROM tblGroupBusiness WHERE Month(CompleteDate) between Month(DateAdd("m",-2,date())) AND Month(Date)

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

CERTIFIED EXPERT

Commented:
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 Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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 JamesTherapist

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!
Then please mark as answered.

Have a nice weekend.
Software & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Dale JamesTherapist

Author

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 JamesTherapist

Author

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

Sincerely

Dale

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.