count the number of records between peak time 1

[serino64]

I have a report that is grouped by phone numbers. The report provides me with infomation on the use of the phone number such has how many calls were made to any particular number, the time of day calls were made, and the cost per call.

My report is based off a table with the following fields and grouped by the phone number field:

PhoneNumber
Time (medium format)
cost

Is it possible to place a textbox on a report and place some code within it to give me the count of calls that were made between peak time 7:00am to 7:00pm based off the time field for the phone number that is grouped in the report?

 

[genomon]

Look up the DCount() function in Access help, and add a BETWEEN clause to the last parameter. (actually, WHERE PhoneNumber = yourvariable AND time BETWEEN mintime AND maxtime). Just pseudocode, but you get the idea...

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[serino64]

Thanks for the reply. I tried this but I get a syntax error.
=DCount("[Query_Employee]","[Time]WHERE PhoneNumber = time AND time BETWEEN "7:00 am" AND 7:00 pm")

Any other suggestions?

 

[dhookom]

DCount() would not be very efficient in this instance. I would use an expression like
=Sum(Abs([Time] Between #7:00# AND #19:00#))
This assumes you want to count specific records within the report.

Your problems with your expression:
[ol]
[li]you have to specify first the field you are counting. This field would be before the query/table name.[/li]
[li]the final argument is the where condition but it shouldn't include the word "where". It's only the condition.[/li]
[li]time and date values must be delimited with "#". [/li]
[li]Your query might not contain the same records as the report[/li]
[/ol]

Duane MS Access MVP

 

[serino64]

Dhookom,

I applied this code =Sum(Abs([Time] Between #7:00# AND #19:00#))within the control source of the text box and I recieved this error message.

Extra )In query expression 'First([=Sum(Abs([Time] Between #7:00# AND #19:00#))])'

I am using Access 97.

 

[serino64]

Dhookom,

Never mind I figured it out. What you suggested did indeed work. Thank you so much for your help.