Date Range for a Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a query for a report that calls only the current fiscal
year data. Example; 7/1/04 - 6/30/05. Do you know how I could build a query
without having to enter a date range manually? A table I'm using does have
the dates in it. Example; inspection date 11/01/04. Thanks.
 
There are a lot of ways to do this, but I'd do the following. Add two
functions to a general module:

Function FiscalYearEnd(TheDate As Date) As Date
If Month(TheDate) >= 1 And Month(TheDate) < 7 Then
FiscalYearEnd = DateSerial(Year(TheDate), 6, 30)
Else
FiscalYearEnd = DateSerial(Year(TheDate) + 1, 6, 30)
End If
End Function

Function FiscalYearBegin(TheDate As Date) As Date
If Month(TheDate) >= 1 And Month(TheDate) < 7 Then
FiscalYearBegin = DateSerial(Year(TheDate) - 1, 7, 1)
Else
FiscalYearBegin = DateSerial(Year(TheDate), 7, 1)
End If
End Function

These functions will find the beginning and ending dates of whatever date is
provided it. To use them, (and I am assuming you want values from THIS
fiscal year), just use the built in Date() function as the parameter value.
Like this:

SELECT * FROM MyTable WHERE InspectionDate BETWEEN FiscalYearBegin(Date) AND
FiscalYearEnd(Date)

The beauty of these functions is if you gave any date in the last fiscal
year, it will return last year's fiscal begin and end dates.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
You might be able to use the DLookup function to get the desired date from
the table and use it in the criterion. Check it out in Help files and post
back if you have questions.
 
Thank you Rodger,

I am not too good with vba. I already designed the report but don't know
where to insert the information you provided. I tried under an event
proceedure but it did not work. Can I insert it in the query builder for the
report? Your help is appreciated.
 
You want to put the two functions in a General Module. That is, click the
Modules Tab on the Database Window, then click New. Copy and paste the
functions into this module and save it (you can use any name you like, but I
suggest "basFiscalYear"). Then you can use the functions as I showed in the
query.

Does that make more sense?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Roger,
I've got the module built. I'm stuck getting the module inserted into the
report. How do I do that? I tried going to the report properties and create
an event proceedure but was unsuccessful. I don't know what you mean when you
say include the module in the query. Your help is much appreciated.
 
Back
Top