Date Range Query Information in a Report

[Andy Stephens]

Ok, I have battled this one for years and have never found an answer.

I have query criteria for a report Between [Enter Begin Date] And [Enter
End Date]

How would I get the Begin Date and End Date on the report Header?

Thanks,
Andy

 

[Kernow Girl]

Hi Andy - put a Text Box in the header and in it put =[enter begin date],
then in a second text box put =[enter end date] - When you run your report
the query runs first and so has a field called [enter begin date] for you to
use on the report. The only thing is that it must be EXACTLY what you have in
your query.
Hope this helps --- Dika

 

[Andy Stephens]

Dika,

Thank you so much for your help. I have tried that before and some reason
it never worked for me. Must have had my code wrong before.

Now my next problem is I am calculating how many hours people missed for
being absent or leaving early or late or voluntary time off...

So if there is 250 hours of VTO hours make Access say 250:00:00 GRRR

 

[Kernow Girl]

Hi Andy - glad it finally worked and yah, it has to be exact. I also
sometimes get a weird problem where it comes up and asks for the date twice,
but if I close the system down and go back in it goes away. Not sure why.

As for your other problem - I'm assuming you mean the VTO is the time
between the 2 dates? If so you can use the dates in a single text box and
build a formula to work out the hours, can use in an IIF statement to put in
the text.

So in the box you could put something like this ---
=(datevalue([Enter Begin Date])-datevalue([Enter End Date]))
the DateValue turns the your Begin and End into dates not text values and
you can then add, subtract, etc. This would give you the totlal number of
hours.

then if you combine it with an IIF statement

=IIF(Datevalue([Enter Begin Date])-Datevalue([Enter End Date]) => 250,
Datevalue([Enter Begin Date])-datevalue([Enter End Date]) & " GRRR",
datevalue([Enter Begin Date])-datevalue([Enter End Date]))

it will then say 250 GRRRR if 250 or over and just the number if under.

I put these formula in the underlying Query for the report rather than on
the Report Layout itself as you can then use the Query for other reports and
not have to re-type the formula. Also look at DateDiff.

Hope this was what you needed and that is helps - yell if not.

Yours --- Dika

 

[Andy Stephens]

LOL

the GRRRR was me growling at Access....I love Access but man do I hate it
sometimes. It is the pickiest piece of software!!!

I used the DateDiff function in my queries to get the numbers of mintues i
needed to calculate then I used another code to format it into hours and
minutes.

The following is my codes in my queries for others to use if needed:

Early Minutes: DateDiff("n",[Left Time],[End Time])
Early Real Time: IIf([Early Minutes]<0,[Early Minutes]+1440,[Early Minutes])
VTO Minutes: DateDiff("n",[VTO Time],[End Time])
VTO Real Time: IIf([VTO Minutes]<0,[VTO Minutes]+1440,[VTO Minutes])
Late Minutes: DateDiff("n",[Start Time],[Arrive Time])
Late Real Time: IIf([Late Minutes]<0,[Late Minutes]+1440,[Late Minutes])

In the reports:

I made a Sum text box to calculate the total mintues from the previous codes
then I used the code: =[Total Minutes]\60 & Format([Total Minutes] Mod
60,"\:00")
to format the minutes into hours and minutes.

The Minutes first calcuates the time the employee missed by either being
late, leaving early, or taking voluntary time off. The Real Time converts
negative time incase the times enter fall between 2 days (i.e. left at 23:00
but shift ended at 2:00 the next day)

All kudos for the above go to Allen Brown for his webpage. Totally helped
me out there.

Thanks again Dika

 

[terri]

i add a textbox that has this:

="Between " & [Beginning Date] & " and " & [End Date]

and the date range always shows up