Financial Year Ordering

  • Thread starter Thread starter syd053
  • Start date Start date
S

syd053

Guest
I am currently creating a sales analysis report and i am ordering the figures by the month and year. What i need to do is to order the report for the financial year (Apr-Mar) Is this possible and how would i go about doing this. Your help would be greatly appreciated

Thanks

Martin
 
If you sort by month and year won't they still be in the same order even if the financial year starts in April?
 
Try creating a function that passes the month and "renumbers".

Function ReturnMonthOrder(vMonth) As Integer
Dim tmpOrder As Integer
Select Case vMonth
Case 1 'Jan is set to 10
tmpOrder = 10
Case 2 'Feb is set to 11
tmpOrder = 11
Case 3 'Mar is set to 12
tmpOrder = 12
Case 4 'This is April so return this as 1
tmpOrder = 1
Case 5 'May is set to 2
tmpOrder = 2
'Continue Case Statement through remaining Months
.
.
.
End Select
ReturnMonthOrder = tmpOrder
End Function
Now use the function in your query behind the report and order by this field.
 
To provide an example, create a new query in Northwind and copy/paste in this SQL
Code:
PARAMETERS [Enter FYStartDte] DateTime;
SELECT Orders.OrderID, 
Int(DateDiff("m",[Enter FYStartDte],[OrderDate])/3)+1 AS MyQtr, 
Orders.OrderDate
FROM Orders
GROUP BY Orders.OrderID, 
Int(DateDiff("m",[Enter FYStartDte],[OrderDate])/3)+1, 
Orders.OrderDate
HAVING (((Orders.OrderDate) Between [Enter FYStartDte] And DateAdd("yyyy",1,[Enter FYStartDte])-1));
Run the query and, when prompted, enter 4/1/95 (if you routinely use US Short-Date Format, or 1-Apr-95 (medium date format) for everyone else).

Ideally, you'll be rewarded with records between 1-Apr-95 and 31-Mar-96 (as the result of the
Code:
HAVING (((Orders.OrderDate) Between [Enter FYStartDte] And DateAdd("yyyy",1,[Enter FYStartDte])-1));
...portion, with their respective FY quarters displayed in response to
Code:
 Int(DateDiff("m",[Enter FYStartDte], [OrderDate])/3)+1,

I hope that this will be enough to get you up and running but, if not, please post back.

Best wishes,

Bob
 

Users who are viewing this thread

Back
Top Bottom