Between Start Time and End Time

[libby32]

We have shifts that run from 6:00 one morning until 5:00 the next morning. I will need a report printed on demand where that user can enter the First Date and Last Date - this is no problem with a parameter but how do I only get the report to include the right start time and finish time??

The report is based on a query...

 

[Rob.Mills]

If you've got it working in the query and the report is based off the query then the report should work too, yes?

 

[Shadez]

You can use the where Property on the report.

if u use

docmd.openacreport "MyReport",acpreview,,"Mydate >=#" & mystartDate & "# and Mydate <= #" & MyEnddate & "#"

to open ur report.

the other way is to get ur query to take the start and end date from your form.


ShadeZ

 

[libby32]

Between start time and date

sorry for the confusion, the report is based on a query but I can not get it to work in the query... I need for the user to enter a start date and time of 6:00 a.m then enter an end date and time of 5:00 a.m.

 

[Mile-O]

Have the tables you are querying on stored the information like this?

26/03/03 05:00:00

In this case, just putting the date in won't return a value if you are entering the parameters.

 

[libby32]

between 2 dates and times

Th data is in the query in this format 03/23/2003 6:00:00. I have a parameter query in this field . I'm not getting the results.

 

[Mile-O]

I think you should add two calculated fields to your query making use of the DateSerial() function.

i.e

StartOfShift: DateSerial(Year([YourShiftStartField]), Month([YourShiftStartField]), Day([YourShiftStartField]))

EndOfShift: DateSerial(Year([YourShiftEndField]), Month([YourShiftEndField]), Day([YourShiftEndField]))

This way you can put the criteria on these two fields and you should get results.

 

[libby32]

between times

Thanks for your reply...How should I handle the time? Should I just put this as a parameter under the two caculated time fields Like this

Between [Enter Start Time] and [Enter End Time]

 

[Jon K]

Try this.

In query Design View, type the Criteria:-

Between [Enter First Date]+#6:00:00 AM# And [Enter Last Date]+#5:00:00 AM#


Then declare the Data Type of the two parameters:-

Choose menu Query, Parameters...
Type parameter Enter First Date, select data type Date/Time
Type parameter Enter Last Date, select data type Date/Time