restricting report by range of date

[Michelle]

Hi all

I have a report which displays staff absences. An absence record has
a start and end date and the type of absence, ie sickness, annual
leave.

I have created a form which allows the user to specify criteria for
viewing the report. The one I am having problems with is the date. I
allow the user to set a date range, so for example, the user selects
01/03/04 to 08/03/04 and I want to be able to show all absences that
fall within that date so in effect the report gives me a complete
picture of who is not available during that week.

so for example if an employee starts leave on 29/02/04 and ends on
2/03/04 i want this to show on the report

would be grateful for any help.

thanks
michelle

[Wayne Morgan]

In the query feeding the report, set the two controls on the form as the
criteria for the date range.

Example:
Between Forms!Form1!txtDateFrom And Forms!Form1!txtDateTo

I noticed that you are not using the US date format. I don't do
internationalization, but from what I've read there are times that you have
to use the US date format to get things such as this to work. If that is the
case, change the above to

Between Format(Forms!Form1!txtDateFrom, "mm/dd/yyyy") And
Format(Forms!Form1!txtDateTo, "mm/dd/yyyy")

The format command changes the date to a string so you may need to also
concatenate in # signs as a date delimiter.

Between "#" & Format(Forms!Form1!txtDateFrom, "mm/dd/yyyy") & "#" And "#" &
Format(Forms!Form1!txtDateTo, "mm/dd/yyyy") & "#"

--
Wayne Morgan
Microsoft Access MVP
"Michelle" <mi*************@poferries.com> wrote in message
news:72**************************@posting.google.c om...

Hi all

I have a report which displays staff absences. An absence record has
a start and end date and the type of absence, ie sickness, annual
leave.

I have created a form which allows the user to specify criteria for
viewing the report. The one I am having problems with is the date. I
allow the user to set a date range, so for example, the user selects
01/03/04 to 08/03/04 and I want to be able to show all absences that
fall within that date so in effect the report gives me a complete
picture of who is not available during that week.

so for example if an employee starts leave on 29/02/04 and ends on
2/03/04 i want this to show on the report

would be grateful for any help.

thanks
michelle

[Lyle Fairfield]

mi*************@poferries.com (Michelle) wrote in
news:72**************************@posting.google.c om:

Hi all

I have a report which displays staff absences. An absence record has
a start and end date and the type of absence, ie sickness, annual
leave.

I have created a form which allows the user to specify criteria for
viewing the report. The one I am having problems with is the date. I
allow the user to set a date range, so for example, the user selects
01/03/04 to 08/03/04 and I want to be able to show all absences that
fall within that date so in effect the report gives me a complete
picture of who is not available during that week.

so for example if an employee starts leave on 29/02/04 and ends on
2/03/04 i want this to show on the report

air code

condition

WHERE fldBeginDateTime <
Format(YourForm.StopRange.Value + 1, "\#mm\/dd\/yyyy\#")
AND fldEndDateTime >=
Format(YourForm.StartRange.Value, "\#mm\/dd\/yyyy\#")

"Between" can lead to grief with dates when someone enters a time value
into one of the dates.

Yes, I did mean (begin < stop+1) and (end >=start).

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Michelle Beatty]

Thanks Wayne

However, i'm not totally sure that this will work. This will make sure
that anything with a start or end date between the two dates mentioned
on my form is captured. However, what about for an absence that starts
before the dates entered or ends after the dates entered. How would i
capture these.

cheers
michelle

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Michelle]

Thanks Lyle

that worked a treat!!!!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[PC Datasheet]

In the query for the report, put this expression in the criteria of the
LeaveDate field:

Between Forms!NameOfFormToEnterDates!StartDate And
Forms!NameOfFormToEnterDates!EndDate

Open the form to enter dates in the Open event of the report:
DoCmd.OpenForm "NameOfFormToEnterDates"

Put an OK button on the form to enter dates and put this code in the Click
event:
Me.Visible = False

Close the form to enter dates in the Close event of the report:
DoCmd.Close acForm, "NameOfFormToEnterDates"
DoCmd.Close 'this is for the report
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Michelle" <mi*************@poferries.com> wrote in message
news:72**************************@posting.google.c om...

Hi all

I have a report which displays staff absences. An absence record has
a start and end date and the type of absence, ie sickness, annual
leave.

I have created a form which allows the user to specify criteria for
viewing the report. The one I am having problems with is the date. I
allow the user to set a date range, so for example, the user selects
01/03/04 to 08/03/04 and I want to be able to show all absences that
fall within that date so in effect the report gives me a complete
picture of who is not available during that week.

so for example if an employee starts leave on 29/02/04 and ends on
2/03/04 i want this to show on the report

would be grateful for any help.

thanks
michelle