Thanks:  0
Likes:  0

# Thread: Determine Fiscal Year (FY) Quarter Based on Dates

1. ## Determine Fiscal Year (FY) Quarter Based on Dates

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?

2. ## Re: Determine Fiscal Year (FY) Quarter Based on Dates

I guess since the Table is the source data, I would like to ideally have the quarter determined in the Form section.

**bump**

4. ## Re: Determine Fiscal Year (FY) Quarter Based on Dates

You can use the DATEADD and DATEPART functions to return the quarter number for your scenario like this:
Code:
`Qtr: DatePart("q",DateAdd("m",3,[MyDate]))`
Details on DATEPART: MS Access: DatePart Function

5. ## Re: Determine Fiscal Year (FY) Quarter Based on Dates

Where would I need to put this code in? Under Validation Rule in my Table? In my Form?

**delete***

7. ## Re: Determine Fiscal Year (FY) Quarter Based on Dates

Originally Posted by Joe4
You can use the DATEADD and DATEPART functions to return the quarter number for your scenario like this:
Code:
`Qtr: DatePart("q",DateAdd("m",3,[MyDate]))`
Details on DATEPART: MS Access: DatePart Function
In addition to my question above, for the syntax [MyDate] I would need to replace it with the name of the column for my field (Date_of_Award)? Correct?

8. ## Re: Determine Fiscal Year (FY) Quarter Based on Dates

In addition to my question above, for the syntax [MyDate] I would need to replace it with the name of the column for my field (Date_of_Award)? Correct?
Exactly!

9. ## Re: Determine Fiscal Year (FY) Quarter Based on Dates

Originally Posted by Joe4
Exactly!
Where would I need to put the code though? In a query? Table? Form? Please be specific.

Thanks!

10. ## Re: Determine Fiscal Year (FY) Quarter Based on Dates

Where you put it really depends on how you need/plan to use it.
The formula I wrote could be inserted into a query as a Calculated Field (also known as an Expression).
See here for step-by-step instructions on how to do that: Access Tips: Calculating in Queries

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•