By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,686 Members | 2,770 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,686 IT Pros & Developers. It's quick & easy.

restricting report by range of date

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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)
Nov 12 '05 #3

P: n/a
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!
Nov 12 '05 #4

P: n/a

Thanks Lyle

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

P: n/a
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

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.