Fiscal Year to Date

[PenC via AccessMonster.com]

Hello

Im having trouble trying sort some data from a table and structuring it so it
can have our fiscal start date (July 1st) to a rolling current date Im
writing in sql so I can use that query for a crystal report.



Any help will be appreciated, thanks!

 

[akphidelt]

Try creating a query, and having the criteria set to >#6/31/2007# and use
your query as the source to get data

 

[PenC via AccessMonster.com]

Try creating a query, and having the criteria set to >#6/31/2007# and use
your query as the source to get data

Hello

[quoted text clipped - 3 lines]

Any help will be appreciated, thanks!

Thanks for the answer, its not quite what I was looking for, I have 3-4 years
worth of information on the table so I need to show that on the query (not
just the current fiscal). It has sort by its proper fiscal year (july-june)
but only count july-march for all the years since march is the current month.
Hope it makes sense and sorry for the confusion.

 

[John Spencer]

CurrentFiscalYear
Year(DateAdd("m",6,[YourDateField]))
Month
Month([YourDateField])
FiscalMonthNumber
Month(DateAdd("m",6,[YourDateField]))

Field: FiscalMonthNumber: Month(DateAdd("m",6,[YourDateField]))
Order By: Ascending
Criteria: Between 1 and Month(DateAdd("m",6, Date())

Field: FiscalYear: Year(DateAdd("m",6,[YourDateField]))
Criteria: Between 2003 and 2007

You can choose whether or not to show/hide the calculated field.

Of course, I don't know if Crystal Reports can use the DateAdd function. If
not, you may have to add two fields to your table and update them using the
Fiscal Year and Fiscal Month number expressions.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Try creating a query, and having the criteria set to >#6/31/2007# and use
your query as the source to get data

Hello

[quoted text clipped - 3 lines]

Any help will be appreciated, thanks!

Thanks for the answer, its not quite what I was looking for, I have 3-4
years
worth of information on the table so I need to show that on the query (not
just the current fiscal). It has sort by its proper fiscal year
(july-june)
but only count july-march for all the years since march is the current
month.
Hope it makes sense and sorry for the confusion.

 

[John W. Vinson]

Hello

Im having trouble trying sort some data from a table and structuring it so it
can have our fiscal start date (July 1st) to a rolling current date Im
writing in sql so I can use that query for a crystal report.

Let's say you have a datefield in the query and you want to select all records
in a chosen fiscal year. A criterion

= DateAdd("m", -6, DateSerial([Enter fiscal year:], 1, 1)) AND < DateAdd("m", 6, DateSerial([Enter fiscal year:], 1, 1)

will extract just records in that FY.