My Fiscal Quarter 1 starts in April!

[arman_canada]

Hi there, my question is in regards to the Access Fiscal Calender Year. MS Access Fiscal Calender first quarter (Q1) starts on January 1st.

My report for my mini DB has its Q1 starting April 1st to June 30th.

I have the following code in my Report (it came with the Default Wizard Design):

=Format$([Agreement Start Date],"\Qq yyyy",0,0)

Essentially, how can I make it so that my Q1 starts on April 1st?

Thank you in advance for your help.

 

[Banana]

The best thing I can suggest is to create your own custom functions to give you your own fiscal year. (Even better, google and see if someone has written such functions already).

 

[arman_canada]

Hi, thanks for the reply. What would the coding be, can you suggest some?

 

[Banana]

Well, I hope you've already searched the forums or googled for fiscal year functions. I'd expect someone to already write such functions.

But a quick and dirty way would be simply to subtract 3 months from the date and store it thus and when you need to display it again, add 3 month back again. This way all Access's built-in date function will not spill it over from one year to another.

For something more sophicated, you need to tell Access what April 1st means to you and make Access treat it as if it was January 1st in sense of counting years.

If still need more concrete examples, give some details what kind of stuff you will want to do with the date. Do you want the fiscal year to be shown over the calendar year (e.g. Feburary 15th, 2008 would be read as 2/15/07 as it's still in 07 fiscal year?)

 

[arman_canada]

Hello Banana, you are exactly right, those are the approaches that I should take. Thank you. I love the idea about substracting 3 months from the current format! Although what I ended up doing earlier was making a report with two subreports, with each subreport pertaining to the query for the First Quarter and Second Quarter respectively.
Thus far I got a Title labeled Quarterly Earnings for 2007, then I have Quarter 1, with all the info required for the dates in Q1, and then Quarter 2 with all its requirements. Thank you for you help.



I'LL BE BACK!! ...(I will have more questions in the comming weeks)

 

[Rich]

There have been many posts on fiscal year functions, use the search button

 

[MStCyr]

Hi, thanks for the reply. What would the coding be, can you suggest some?

Hi Arman_canada

Please find below a fiscal year year function .... and it does work. I guess you're doing some work for Canadian Federal Gov.

Maurice






Function WOFiscalYear()
Dim mydb As Database, MyTable As Recordset
Dim StoredFiscal_Year As String
Dim Counter As Long
Set mydb = CurrentDb
' Return database object.
Set MyTable = mydb.OpenRecordset("tbl_WO_Counter") ' Open Table.
StoredFiscal_Year = MyTable![Fiscal_Year]
Counter = MyTable![Counter]

Dim newproj1, newproj2 As String

If DatePart("m", Date) < 4 Then
newproj1 = Mid(DatePart("yyyy", Date), 3, 2)
newproj2 = Mid(DatePart("yyyy", Date - 365), 3, 2)
ElseIf DatePart("m", Date) >= 4 And DatePart("d", Date) >= 1 Then
newproj2 = Mid(DatePart("yyyy", Date), 3, 2)
newproj1 = Mid(DatePart("yyyy", Date + 365), 3, 2)
End If

If StoredFiscal_Year = newproj2 & "/" & newproj1 Then
'they are equal so do nothing..
Else
'they are not equal, so update the Fiscal_Year field with new value
'and reset the WO counter to 1 ---- start of new fiscal year
With MyTable
.Edit
MyTable![Fiscal_Year] = newproj2 & "/" & newproj1
MyTable![Counter] = 1 'restart counter for NewYear
'MyTable![Counter] = MyTable![Counter] + 1 'keep on going from the old counter
.Update
End With
End If

WOFiscalYear = newproj2 & "/" & newproj1
End Function