Creating a Fiscal Year query based on user supplied year 2

[Klepdog]

I am trying to create a query from the query design menus that will return all records based on a user supplied fiscal year. I have been able to create one for the current fiscal year but not past years(i.e. 2010, 2009,etc...). My current fiscal year criteria is as follows:

Between DateSerial(Year(Date())-1,10,1) And DateSerial(Year(Date()),9,30)

How can I modify this for past years and at the same time ask the user for the year he wants for this query? The field name is SchedDate and it is in a medium date format.

 

[dhookom]

I would filter on the year of the date field minus 10 months. Assuming your user enters the year in a control on a form, try:

WHERE Forms!frmEnterYear!txtYear = Year(DateAdd("m",-10,[SchedDate]))

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Another way:
Between DateSerial(Year([!]Forms!frmEnterYear!txtYear[/!])-1,10,1) And DateSerial(Year([!]Forms!frmEnterYear!txtYear[/!]),9,30)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Klepdog]

I have tried both ways and they both work. Thanks guys.