Printing reports for a select year

[iamdamien]

Hi,
I am currently printing reports on a select monthly basis, using some code like below:

WHERE (((Format([SaleDate],"yyyy"))=Forms![Sales Search]!cbo_Year) And ((Format([SaleDate],"mm"))=Forms![Sales Search]!Cbo_Month));

What is the best way to generate a report for a year or financial year (April 6 to April 5, UK fiscal year).

 

[DCrake]

"Where [YourDate] Between #06/04/2011# And #05/04/2012#"

 

[iamdamien]

Legendary, thanks for that.

 

[iamdamien]

But if I make the "year"; user input dependant, would that be something like:
WHERE [Date] between #06/04/"cbo_year"#? Or is that done in some other way?

 

[DCrake]

Here is a function that will calculate the financial year of the date entered based on 1st April to 31st March

Public Function FinancialYear(AnyDate As Date) As String


If Month(AnyDate) < 4 Then
    FinancialYear = Year(DateAdd("yyyy", -1, AnyDate)) & "/" & Year(AnyDate)
Else
    FinancialYear = Year(AnyDate) & "/" & Year(DateAdd("yyyy", 1, AnyDate))
End If
End Function

So today would be

finToday = FinancialYear(Date())

finToday = "2011/2012"

If you apply this function to a date field in a query and group by that date you can select the financial year based on actual data.

 

[iamdamien]

So using that above code; what would be the best way to implement a selection method for a combobox of years? So that the user can select for instance "2010/2011" from a combobox and press a select button. This function then generates the report for april 2010 until april 2011? Apologies if i did not make this clear from the beginning.

 

[Access N0vice]

I am not sure if any one is watching this post anymore but I have a question where exactly are you suppose to put the function statement stated above?

 

[boblarson]

I am not sure if any one is watching this post anymore but I have a question where exactly are you suppose to put the function statement stated above?

In a STANDARD module (not form, report or class module) with the module named something different from the function.