change from calender year to fiscal year

[1brainylack]

Hi, I'm trying to design a summary query based on fiscal quarters, instead of calendar quarters. Is this possible?

 

[tlbroadbent]

You could write your own function.

Public Function FiscalQuarter(TheDate As Date) As Integer
'Assume fiscal year starts on July 1
Select Case DatePart("m", TheDate)
Case 7 To 9
FiscalQuarter = 1
Case 10 To 12
FiscalQuarter = 2
Case 1 To 3
FiscalQuarter = 3
Case 4 To 6
FiscalQuarter = 4
Case Else
'Error condition
FiscalMonth = 0
End Select
End Function
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[1brainylack]

thanks, but i can't seem to get this to work. i am new to the whole function writing thing. more specifics please.

 

[tlbroadbent]

Create the function in a VB module. Use the function in a query as follows.

Select FiscalQuarter(DateCol), Sum(Amt) As QtrTot
From Table1
Group By FiscalQuarter(DateCol) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[1brainylack]

i've entered it into the vb module, but keep getting an error. Compile Error: Unexpected end of statement. i'm certain i've done something wrong, but what? thanks any help.

 

[tlbroadbent]

Will you post your function, please? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[1brainylack]

I did finally get it to work! Thanks for all your help!

 

[rookiedev]

I HAVE A SIMILAR NEED.I NEED TO DISPLAY COSTS FOR MATERIALS FOR A FISCAL YEAR. I HAVE SET UP A CROSS TAB QUERY TO CAPTURE THE COST BY CALENDAR YEAR BUT OUR FISCAL YEAR RUNS FROM 1 APRIL THROUGH 31 MARCH. I'M HOPING THERE IS A SIMPLE WAY TO DO THIS. CAN YOU HELP???
rookiedev

 

[tlbroadbent]

Rookie,

What have you tried so far? What problems have you encountered? What exectly is your question? We can't develop an application for you but can guide you through some steps if you have difficulties. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[rookiedev]

I used this expression as a field in my crosstab query

Exp1: Year([OrderDate]) & " " & "Order Total"

This gives me Column Headings of "2000 OrderTotal","2001 Order Total", "2002 Order Total"

Here is the problem: I need the 2000 Order Totals to capture the data from 1 April 2000 through 31 2001 March and 2001 Order Total to be from April 1 2001 through 31 March 2002 and so on and so forth.......

Am I asking to much of Access to do this?

I really appreciate your help!
Rookiedev