Determine Fiscal Year (FY) Quarter Based on Dates

[legalhustler]

I have a MS Access table where one column has dates i.e. 12/1/12, 7/15/14 and another column where I need the quarter for each date. The quarters are based on the following months:

Q1 = 10,11,12
Q2 = 1,2,3
Q3 = 4,5,6
Q4 = 7,8,9

Is there a formula that I can put in the Validation Rule (under the Field Properties) or something to have this field be automatically determined? If not, will it only work in a query?? Is there a VBA for this?

 

[legalhustler]

You can use the DATEADD and DATEPART functions to return the quarter number for your scenario like this:

Qtr: DatePart("q",DateAdd("m",3,[MyDate]))

Details on DATEADD: DateAdd Function - Access
Details on DATEPART: MS Access: DatePart Function

I created a query based on my table and selected Date_of_Award (which has various dates for different years and is in mm/dd/yyyy format) and added the new field using the calculation you provided, but that creates a parameter query which asks me to put a date and it would make all the Date_of_Award field return the same quarter. I need a calculated field that determines the fiscal quarter based on the month for the Date_of_Award, as follows:

Q1 = 10,11,12
Q2 = 1,2,3
Q3 = 4,5,6
Q4 = 7,8,9

For example, the date 05/21/2012 should be Q3, 12/03/2014 should be Q1, etc.

 

[Joe4]

but that creates a parameter query which asks me to put a date and it would make all the Date_of_Award field return the same quarter.

It should NOT create a parameter query, unless you have an error in your formula.
If it is asking for a parameter, it means that it does not recognize the field name you entered.
You have either not changed the Field name to match what you have named it (replace "MyDate" with the name of your field, like we discussed earlier), or you have a typo in your Field name.
Fix that, and your problems should be solved.

 

[legalhustler]

It should NOT create a parameter query, unless you have an error in your formula.
If it is asking for a parameter, it means that it does not recognize the field name you entered.
You have either not changed the Field name to match what you have named it (replace "MyDate" with the name of your field, like we discussed earlier), or you have a typo in your Field name.
Fix that, and your problems should be solved.

Thanks a bunch! I had an extra space in my Field Name. It worked beautifully.

The formula looks so simple, but for understanding purposes I don't see how it determined it the Fiscal Quarter correctly? How did it know the start and end of the fiscal month for each quarter? What does the number "3" signify?

Thank you again.

 

[Joe4]

Take a look at the DATEADD link I posted back in post #4, especially the example they give. I think once you look at that, it should become immediately evident.
If not, post back, and I'll explain in further.

 

[legalhustler]

Take a look at the DATEADD link I posted back in post #4, especially the example they give. I think once you look at that, it should become immediately evident.
If not, post back, and I'll explain in further.

Yeah makes sense now. Now I'm having trouble looking up the Quarter query in my Table. I get the following message

 

[Joe4]

Now I'm having trouble looking up the Quarter query in my Table. I get the following message

Your error message didn't come out.

Not sure what you mean when you say "looking up the Quarter query in my Table". Typically, a query is all the table fields you want to see and any calculations, filters, etc. You then would use the query for whatever you are trying to do (Forms, Reports, Export, etc). There is no need to go back to the root Table. The Query itself is a link back to the Table.

 

[legalhustler]

***delete***

 

[legalhustler]

Your error message didn't come out.

Not sure what you mean when you say "looking up the Quarter query in my Table". Typically, a query is all the table fields you want to see and any calculations, filters, etc. You then would use the query for whatever you are trying to do (Forms, Reports, Export, etc). There is no need to go back to the root Table. The Query itself is a link back to the Table.

I'm having trouble looking up the calculated query in my Table when I use the Lookup wizard in the design view of my Table. I get the following message "No valid fields can be found in 'Fiscal Quarter'. You may have selected a query that uses the table you're adding the lookup column to. Please select a new source."

I want the calculated field in my query to show in my Table because I'll be creating a Form based on the Table. I was thinking the Quarter field next to the Date field would be nicer to have in a Table for analysis purpose etc, but I guess I could create a Form by combining both the Table & Query like you mentioned.

 

[Joe4]

I want the calculated field in my query to show in my Table because I'll be creating a Form based on the Table. I was thinking the Quarter field next to the Date field would be nicer to have in a Table for analysis purpose etc, but I guess I could create a Form by combining both the Table & Query like you mentioned.

You are going about this a bit wrong. You can create Forms from Queries just as easily as you can Tables. The Query is already linked to the Table, so there is never a need to include both.

All you need to do is have a Query based on your Table that includes all of the fields you wish to show on your Form, and your calculated Field. Then use this Query as the Data Source of your Form.

Pretty much anything you use a Table as a Data Source for in Access, you can use a Query as a Data Source too.

 

[legalhustler]

You are going about this a bit wrong. You can create Forms from Queries just as easily as you can Tables. The Query is already linked to the Table, so there is never a need to include both.

All you need to do is have a Query based on your Table that includes all of the fields you wish to show on your Form, and your calculated Field. Then use this Query as the Data Source of your Form.

Pretty much anything you use a Table as a Data Source for in Access, you can use a Query as a Data Source too.

Totally makes sense (i.e. add ALL fields in my query). I was focusing too much on a single tree rather than looking at the whole forest

Thanks again.