Date Range for a Report

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.
 
R

Roger Carlson

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
 
K

Ken Snell [MVP]

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.
 
G

Guest

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.
 
R

Roger Carlson

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
 
G

Guest

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Get fiscal year to show in a report 4
Picking up dates from a parameter 3
How can I compare yearly shipped side by side (column) per part nu 1
Adding a Date Range to a Report 1
Reporting - Breakdown date range output 4
print a date range from parameter query on a report header? 2
Limit data in field to within 90 days of another field... 4
Adding Date Range to Report 1

Top