Date Help

[Bre2004]

I need to create a field that takes my transaction date and turns it into the correct quarter. Our first Quarter starts October 1st. So if the transaction date was 10/22/2014, I would want my new field to show quarter 1. Then if we had a transaction for January 22, it would then show Quarter 2.



Thanks in advance for your help.

[lfpm062010]

This is one way to do it.

Create a table "QUARTERLOOKUP" with Field "STARTDATE", "ENDDATE", "QUARTER".
STARTDATE ENDDATE QUARTER
10/1/2014 12/31/2014 1
1/1/2015 3/31/2014 2
4/1/2015 6/30/2015 3
7/1/2015 9/30/2015 4
10/1/2015 12/31/2015 1

Create a query and look up the "QUARTERLOOKUP" table and find your quarter.

SELECT QUARTER FROM QUARTERLOOKUP WHERE [TransactionDate] BETWEEN [START DATE] AND [END DATE];

[Bre2004]

It tells me I have a Data type mismatch in criteria expression. Here is how I have it written.

"where [Tran_Date]between[startdate]and[enddate]"

[lfpm062010]

What is "STARTDATE" and "ENDDATE"? DATE/TIME???

Did it ask you to fill in the "Tran_Date"? or did you get "Tran_Date" from a form or table or ...?