Using a date expression in query (1 Viewer)

I have a table with field DateAdded, and I would like to query the table for only those records where the date is in the current quarter. I can not find an example of how to write the expression for this and whether to put the expression in the criteria of the DateAdded field or to create a field for it like this: QuarterExpression)..., with a criteria of ?, Today's date?
Please advise.
Thanks

FYI: This is for 2013 Web App so expressions don't always have the same syntax as the native client.

assuming the first quarter is 1st Jan to 31st Mar then the quarter can be calculated for todays date as

((month(date())-1) \ 3)+1

but this would provide the same value for last year, so to get a true current quarter you may need to include the year - it depends on what your other criteria are

year(date())+((month(date())-1) \ 3)+1

See if this helps, and works in a web app.

https://support.microsoft.com/en-us/kb/210604

Hi,

This expression should work fine for you in your Access web app context; it worked OK in my test to return the records in the current quarter.
Just put this expression in the Criteria line under the DateAdded field in your query:

Year([DateAdded])=Year(Now()) And DatePart(Quarter,[DateAdded])=DatePart(Quarter,Now())

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
Senior Content Developer - Modern Assistance and Support Experience - Microsoft Corporation

Author - Microsoft Access 2013 Inside Out
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010/2013 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/en-us/legal/Copyright/Default.aspx
----------

AccessJunkie said:

Hi,

This expression should work fine for you in your Access web app context; it worked OK in my test to return the records in the current quarter.
Just put this expression in the Criteria line under the DateAdded field in your query:

Year([DateAdded])=Year(Now()) And DatePart(Quarter,[DateAdded])=DatePart(Quarter,Now())

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
Senior Content Developer - Modern Assistance and Support Experience - Microsoft Corporation

Author - Microsoft Access 2013 Inside Out
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010/2013 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/en-us/legal/Copyright/Default.aspx
----------

Click to expand...

Thank you!!! That did the trick

GingGangGoo said:

Thank you!!! That did the trick

Click to expand...

How do I set this to look for previous quarters?
Thanks

Can you give an example or 2 showing your use of previous quarters?

GingGangGoo said:

How do I set this to look for previous quarters?
Thanks

Click to expand...

Well the solution above lets me see data by current quarter. What I would like to do is look at data from quarter 1, or 2, etc, 1 being Jan 1 - Mar 31, standard fiscal quarters.

In general, to find a DateAdded (your term) in quarter 1 of 2015


I just picked a Date that was in Q1 of this year.
Change as needed to identify the quarter you want.


OOOps: I just reread your post and saw Web app -so syntax may be off -- I don't know Access web apps.

Hi,

Since you are using an Access 2013 web app, here is the syntax you would need for some various query syntax scenarios using your current field name.

Current quarter:
Year([DateAdded])=Year(Now()) And DatePart(Quarter,[DateAdded])=DatePart(Quarter,Now())

Previous quarter:
Year([DateAdded])*4+DatePart(Quarter,[DateAdded])=Year(Now())*4+DatePart(Quarter,Now())-1

Quarter 1 of current year:
Year([DateAdded])=Year(Now()) And DatePart(Quarter,[DateAdded])=1

Quarter 2 of current year:
Year([DateAdded])=Year(Now()) And DatePart(Quarter,[DateAdded])=2

Quarter 3 of current year:
Year([DateAdded])=Year(Now()) And DatePart(Quarter,[DateAdded])=3

Quarter 4 of current year:
Year([DateAdded])=Year(Now()) And DatePart(Quarter,[DateAdded])=4


You can experiment with those in different queries to make sure they are returning the data you want.

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
Senior Content Developer - Modern Assistance and Support Experience - Microsoft Corporation

Author - Microsoft Access 2013 Inside Out
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010/2013 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/en-us/legal/Copyright/Default.aspx
----------