Counting number of records - time field 1

[Stevehewitt]

Hi Guys,

I have a feeling this is going to be something really easy, or something ridiculously hard. Anyway...!

I have a report which lists a number of records. One of the fields is a "TimeEntered" field. Management want to have the report show how many records there are that were entered in the following time periods:
9am - 12 noon
12 noon - 6pm
6pm - 9pm

How would I go about doing this? Could it be done at query level and then just a SUM in the footer or something else?

Thanks in advance,


Steve.

 

[ProgramError]

Try using the between function

Ian M


Program Error
Programmers do it one finger at a time!

 

[Stevehewitt]

I can't seem to get this to work with the ABS / SUM function that I am trying to use:

=Abs(Sum([TimeCompleted]=BETWEEN #09:00:00# AND #11:59:00#))

(And two more text boxes for the other two time periods)

Access is having none of it. I'm sure it's just because my syntax is incorrect but I can't find my documentation on that sort of expression.

Anyone tell me where I am going wrong?

Cheers in advance,


Steve.

 

[ProgramError]

I presume u mean between the times on the same day too. Is this today or in the past.



Program Error
Programmers do it one finger at a time!

 

[Stevehewitt]

The query it is based on is a parameter query so the date could be either present or past.

Thanks again,


Steve.

 

[ProgramError]

put this in the criteria of the query field

Between #07/04/2005 20:00:00# And #07/04/2005 23:00:00#

Ian M


Program Error
Programmers do it one finger at a time!

 

[ProgramError]

and change the date part accordingly with either a refernce to an inputbox or object on a form

Program Error
Programmers do it one finger at a time!

 

[Stevehewitt]

Thanks for that.

Unfortunatly it would limit us to one time frame - but we require three.
This report will help us display when we have our busiest periods - we're a call centre and every call gets logged into the database. Management want to see how many calls get logged at the three different time periods.

Is there a function that I could use that would count the number of records listed on the report but based on a between value?

Thanks,

Steve.

 

[RoyVidar]

Did you try removing the equal sign?

[tt]=Abs(Sum([TimeCompleted] BETWEEN #09:00:00# AND #11:59:59#))[/tt]

- safety, also 59 seconds?

Roy-Vidar

 

[RoyVidar]

Ouch ouch ouch, could it contain dates too...

dunno, something like this (to continue abs/sum thiniges)

[tt]=Abs(Sum(cdbl([TimeCompleted])-cdbl(int([TimeCompleted])) BETWEEN #09:00:00# AND #11:59:59#))[/tt]

Roy-Vidar

 

[ProgramError]

three separate queries with the times changed. Separate command buttons call the queries.

Output form need 'record source' set to the

'name of the query'

and a unbound textbox to display the number of records has its
'Control Source' set to...

=Count(*)

simple! and it works




Program Error
Programmers do it one finger at a time!

 

[Stevehewitt]

Thanks guys.

It's getting late in the UK and I'm back home now, but I'll give the suggestions a go tomorrow AM.

Thanks again,

Steve.

 

[ProgramError]

It's 11:30pm in the uk so night night steve and just to get you going ive cracked the date problem with the between function.

the criteria you need for the 'timeEntered' field in each query is...

Between DateValue([todaysdate])+#09:00:00# And DateValue([todaysdate])+#11:59:59#

and

Between DateValue([todaysdate])+#12:00:00# And DateValue([todaysdate])+#17:59:59#

and

Between DateValue([todaysdate])+#18:00:00# And DateValue([todaysdate])+#21:00:00#


and change the todaysdate to point to a textbox for its source or you can leave it as it is and an input popup box will appear to put the date you want to search on.

boy that took some working out.

Program Error
Programmers do it one finger at a time!

 

[Stevehewitt]

Morning Guys!

Thanks for you help above. I didn't explain things very well!!!

There is a seperate date field that we use, and the criteria is =Date() so date's arn't a problem. Thanks for your help with it through ProgramError.

Huge thanks goes to RoyVidar. The expression:

=Abs(Sum([TimeCompleted] BETWEEN #09:00:00# AND #11:59:59#))

Did the trick! Superb!!

Thanks all for your time and effort - appreciated.

Cheers,


Steve.