Crosstab Query and Date

[Kathleen Badeau]

I have a crosstab query that pulls data into columns headed by years--problem is that I need to define the years as our fiscal year (10/01/??-9/30/??) rather than the default calendar year. Any quick and easy way to do this?

 

[Rich]

Public Function FinancialYear(dDate As Date) As Integer
FinancialYear = Year(dDate) - IIf(dDate < DateSerial(Year(dDate), 10, 1), 1, 0)
End Function

 

[Kathleen Badeau]

cross tab query and date

IIf(invoice Date < DateSerial(Year(invoice Date), 10, 1), 1, 0)


I entered this line on the expression line of the crosstab query for the date column and though it changed the date on the header to be 10/01/2002, 10/01/2003, it actually did not pull the correct amounts in the correct columns. They are still being pulling into the columns by calendar year

Any ideas--I tried to edit the sql with the full text and kept getting syntax errors

 

[Rich]

You have to create the function in a standard Module.
In your query the column becomes FYr:FinancialYear([Invoice Date])

 

[Kathleen Badeau]

Cross Tab and Date

Thanks Rich! It worked 95%...it still labels it the wrong year (2002 instead of 2003) etc, but the important thing is that it's pulling the amounts into the right columns. What I'll probably do (for now) is fudge it by renaming the column headers in the report (no one will know, hahaha).

Thanks so much for the help, I've never messed with the module section of access before, and with your script, it worked beautifully!