Fiscal Year Help

[tlpierpoint]

I am running into a FY problem with a query I am creating to do running
totals. I have a table with the following pertinent fields: Regional Sales
Manager, OrderValue, and OrderDate.

I created a query that does a running total showing that in month 1, $$$ was
sold vs. Budget. Month 2 adds the sales from month 2 to month 1 and compares
to budgetary number for month 2, etc.

SalesRT: DSum("[OrderValueSum]","qryRunningTotals","[Month]<=" & [Month])

My problem is that the OrderDate field is a date format and when I separate
out the months to do the running total it sets January as month 1. This
would be great, except that our Fiscal Year begins in October, so I need
October to be the initial numbers with each succeeding month to be added up
until FY ending September 30.

I created a query that changes the date for October and returns a value
saying it is the 1st month. How can I build on the following SQL statement
so that it will change all of the other months accordingly?

Month:
IIf(Month([ExpOrderDate])=10,Month([ExpOrderDate])-9,Month([ExpOrderDate]))

Any help is greatly appreciated!

 

[John Vinson]

I am running into a FY problem with a query I am creating to do running
totals. I have a table with the following pertinent fields: Regional Sales
Manager, OrderValue, and OrderDate.

I created a query that does a running total showing that in month 1, $$$ was
sold vs. Budget. Month 2 adds the sales from month 2 to month 1 and compares
to budgetary number for month 2, etc.

SalesRT: DSum("[OrderValueSum]","qryRunningTotals","[Month]<=" & [Month])

My problem is that the OrderDate field is a date format and when I separate
out the months to do the running total it sets January as month 1. This
would be great, except that our Fiscal Year begins in October, so I need
October to be the initial numbers with each succeeding month to be added up
until FY ending September 30.

I created a query that changes the date for October and returns a value
saying it is the 1st month. How can I build on the following SQL statement
so that it will change all of the other months accordingly?

Month:
IIf(Month([ExpOrderDate])=10,Month([ExpOrderDate])-9,Month([ExpOrderDate]))

Any help is greatly appreciated!

Just use this instead:

FiscalMonth: Month(DateAdd("m", 4, [OrderDate]))


John W. Vinson[MVP]

 

[tlpierpoint]

Great! This was just what I needed. Thanks so much.
--
tlpierpoint

I am running into a FY problem with a query I am creating to do running
totals. I have a table with the following pertinent fields: Regional Sales
Manager, OrderValue, and OrderDate.

I created a query that does a running total showing that in month 1, $$$ was
sold vs. Budget. Month 2 adds the sales from month 2 to month 1 and compares
to budgetary number for month 2, etc.

SalesRT: DSum("[OrderValueSum]","qryRunningTotals","[Month]<=" & [Month])

My problem is that the OrderDate field is a date format and when I separate
out the months to do the running total it sets January as month 1. This
would be great, except that our Fiscal Year begins in October, so I need
October to be the initial numbers with each succeeding month to be added up
until FY ending September 30.

I created a query that changes the date for October and returns a value
saying it is the 1st month. How can I build on the following SQL statement
so that it will change all of the other months accordingly?

Month:
IIf(Month([ExpOrderDate])=10,Month([ExpOrderDate])-9,Month([ExpOrderDate]))

Any help is greatly appreciated!

Just use this instead:

FiscalMonth: Month(DateAdd("m", 4, [OrderDate]))


John W. Vinson[MVP]