Creating a moving date range in a Report

[pereav]

Hey guys i was wondering if someone could help me with a problem.

Monthly i have to run a report on a huge patient database that we have for the previous month, the problem is is for every page i either have to enter in an ending date for the month or and ending and starting date showing the data for a span over a year ie (for aprils month report from may 2009 - apr 2010 then next month from jun 2009-may 2010). Is there anyway to
1. automate the end date so i don't have to enter it in every time, meaning entering in the last day of the previous month
2. automate the date range so it does a year range but allows that year range to move as the next month comes around

Thanks so much for the help, i'm sort of new at using access so i've been a little lost in the dark trying to figure it all out. Thanks

Pere

 

[rainman89]

look up the dateadd fuction. This should allow you to do what you want as far as moving from one year to the next.

By ending date for the month do you mean you need to put in say, may 31 2010?

 

[rainman89]

Here is a function that will find the last day of the month

Public Function LastDayInMonth(ByVal AnyDate As Date) As Date
' **************************************************  **********
' Given any valid date, returns the last day of that month
' *****************************
LastDayInMonth = DateAdd("m", 1, DateSerial(Year(AnyDate),
Month(AnyDate),  1) - 1
End Function

 

[pereav]

K, that may work, i'll have to play around with it. Thanks for the help!!! I really appreciate it.... So when i run my report I get these parameter value boxes that pop up where I have to enter in the end date or the date range, so using this would that help get rid of those so i don't have to keep entering in the date? HA sorry i know it's probably a real easy answer, but i haven't been using access for very long. Thanks so much for the help!!

Pere

 

[pereav]

Oh here is a copy of the SQL code that is running

PARAMETERS [Enter End Date] LongBinary;
SELECT Readmissions.ID, Readmissions.Name, Readmissions.[Admission Date], Readmissions.[Discharge Date], Readmissions.Duration, Readmissions.ICU, Readmissions.CVU, Readmissions.CCU, Readmissions.Cause
FROM Readmissions
WHERE (((Readmissions.[Admission Date]) Between [Enter Begin Date] And [Enter End Date]));

Thanks

 

[rainman89]

Do you call your report from a form or something?

If so you can create some textboxes on your form that hold the data then in your query you can put the the textboxes in for your criteria.

 

[boblarson]

1. You can get rid of the parameters definition for this.

2. You can use:

SELECT Readmissions.ID, Readmissions.Name, Readmissions.[Admission Date], Readmissions.[Discharge Date], Readmissions.Duration, Readmissions.ICU, Readmissions.CVU, Readmissions.CCU, Readmissions.Cause
FROM Readmissions
WHERE Readmissions.[Admission Date]) Between DateSerial(Year(Date())-1, Month(Date())+1,1) And DateSerial(Year(Date()), Month(Date())+1, 0);

For the current month (May) this should get everything between Jun 1, 2009 and May 31, 2010 and next month it would be between July 1, 2009 and June 30, 2010.