472,354 Members | 1,541 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,354 software developers and data experts.

restricting report by range of date

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
5 4399
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
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
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

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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to...
1
by: Mike Cooper | last post by:
Hi everyone, This is a tough one. I have a database full of solicitations, identifying a customer and recording initial call, first followup, second followup, etc. My boss want to be able to...
3
by: manning_news | last post by:
Using A2K. I've been asked to modify a report currently requiring only one date parameter to now accept a date range. The main report has 2 subreports and is not bound to a table or query. The...
0
by: Stinky Pete | last post by:
Hi, The db I'm trying to update has a report form from which all other statistical forms and reports are selected/printed by dept, type, cost, number etc. The report form uses a date filter to...
5
by: sara | last post by:
I have reports that run from a form where the user can choose a date range, or they run automatically for a week in the "Weekly Reports" option. I created 2 queries and 2 reports - one query...
1
Altius
by: Altius | last post by:
Access Version: 2003 OS: XP Pro Sorry if this is a bit long winded. First a little info about the database. This database is used to keep track of checkout information from our production...
5
by: ladybug via AccessMonster.com | last post by:
I am trying to create a report using multiple tables. The first table has Employee info in it (name and id's). the next three tables have employee info for training, test, scores, etc. I am...
19
by: ali3n8 | last post by:
Hello I have attempted to create a date range report from a query called qrycustomerinformation. The field that contains the value of my date is called Followup. When i run a report on this it is...
12
smithj14
by: smithj14 | last post by:
I have a form to enter start and end dates then select a worker name to filter a report. This all works fine and when the report is open in preview mode it shows the date range in the txtboxes on the...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it so the python app could use a http request to get...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and credentials and received a successful connection...
1
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
0
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.