Sort records based on Fiscal Year

[stanger1]

I would like to sort records based on fiscal year for a chart. The fiscal year would be 7/01/2003 to 06/30/2004. I would like to sort this based on oldest date to newest date. This query could span several fiscal years. Any help would be greatly appreciated.

 

[RuralGuy]

Add another field to your query called:
FiscYear: Year(DateAdd("m", 6, [YourDateField]))
then sort on this field first before the YourDateField

 

[stanger1]

Thanks for the reply!! Your solution worked great.

 

[RuralGuy]

Glad I could help. Thanks for posting back.

 

[stanger1]

Would this work for a cross tab query as well? I just realized that I have a chart that I need to build based on fiscal year. Your suggestion worked great for my select query, but it does not work on my cross tab query. Any suggestions would be greatly appreciated.

Thanks,
Greg

 

[RuralGuy]

Sorry but I have not played with cross tab queries enough to offer a suggestion. You may want to start another thread so the cross tab guru's will see it.

 

[Rich]

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

 

[stanger1]

Rich,

Thanks for the reply! Where would this code be placed. Please forgive my ignorance.