Dale James
asked on
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
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
ASKER
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
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
SELECT GroupID, GroupName, StartDate, EndDate, CompleteDate FROM tblGroupBusiness WHERE Month(CompleteDate) between Month(DateAdd("m",-2,date())) AND Month(Date)
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)"
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.
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.
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))
SELECT GroupID, GroupName, StartDate, EndDate, CompleteDate FROM tblGroupBusiness WHERE (Month(CompleteDate) BETWEEN Month(DateAdd("m",-2,date(
ASKER
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
just want to thank everyone for their response to my original question and to confirm that all worked perfectly.
Thanks again.
Sincerely
Dale
You are welcome!
Then please mark as answered.
Have a nice weekend.
Then please mark as answered.
Have a nice weekend.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
Once again....thank you to all of you for the time and effort provided to answer my initial question.
Sincerely
Dale
Sincerely
Dale
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. :)