Custom Date Field in Table based on FY

[ALewis06]

How can I create a custom date field that shows the quarter based on a fiscal year that runs April-March?

Q1 April - June
Q2 July - Sept, etc

 

[plog]

First and foremost, this custom field should not be in a table. Since it is derived from another field, you only need that initial field and then you derive the Quarter each time you need to use it. You don't store redundant data.

With that said, this is the code you would need to do that:

FiscalQuarter: ((DatePart("q",[YourDateFieldHere])+2) Mod 4)+1


Create a query based on your table, paste that into an open field and replace 'YourFieldNameHere' with the name of your field which contains the date on which the fiscal quarter is based and run the query.

 

[ALewis06]

As is evident I am no Access 2010 expert so thank you for instruction, plog!