When calls are being made

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dylan Moran - 28 May 2006 06:11 GMT

Hi,

I have a system that records when a customer calls.

The date / time is entered (in one field formatted as date time).
eg: 27/05/2006 6:02:03 PM

I want a query to count the number of calls that occur between certain
timeframes.

For example, the number that occur between 8-10 am, or 10 - 12, or 12 - 2pm,
2pm - 4pm etc. Preferabbly all on one report.

So that I can get a report that says

We received 100 calls from the hours of 8am to 10am
We received 400 calls from the hours of 10am to midday.
etc

This I want **not** just for one day, but for a selected date range. So I
guess I am asking for something that can only query the HH:MM:SS of the date,
and essentially ignores the DD:MM:YY part of the date.

Any assistance is appreciated.

Signature

I may not know VBA inside out, but from the outside I am looking in.
Dylan Moran - Melbourne Australia

Reply to this Message

Allen Browne - 28 May 2006 09:33 GMT

You can get the hour of the call from:
DatePart("h", [CallDateTime])
where CallDateTime represents the name of your date/time field.

If you want this in 2-hour blocks, use integer division:
Block: 2 * (DatePart("h", [CallDateTime]) \ 2)

1. Create a query using this table.

2. Depress the Total icon on the toolbar.
Access adds a Total row to the grid.

3. Type the expression above into the Field row.
Access Group By in the Total row.

4. Drag your primary field into the grid
In the Total row, choose Count.

5. Drag the CallDateTime field into the grid.
In the Total row, choose Where.
In the Criteria row, enter:
Between [StartDate] And [EndDate]

6. Save the query, and use it as the source for your report.

If you prefer, you can use a form to supply the StartDate and EndDate.
Details of this technique:
http://allenbrowne.com/casu-08.html

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi,
>
[quoted text clipped - 22 lines]
>
> Any assistance is appreciated.

Reply to this Message

Allen Browne - 28 May 2006 09:35 GMT

Sorry, Dylan, step 5 needs modifiying because your date field has time
values.

The criteria should be:
>= [StartDate] And < ([EndDate] + 1)

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> You can get the hour of the call from:
> DatePart("h", [CallDateTime])
[quoted text clipped - 24 lines]
> Details of this technique:
> http://allenbrowne.com/casu-08.html

>> Hi,
>>
[quoted text clipped - 21 lines]
>> date,
>> and essentially ignores the DD:MM:YY part of the date.

Reply to this Message

Dylan Moran - 29 May 2006 05:50 GMT

Hi Allen,

Thanks very much. Once again your skills and knoweldge are just unbelieveable.
If you dont remember me, you have assisted me so many times during the
various number of Access solutions I have developed, I feel like you should
be on the payroll. Your commitment to helping others is wonderful.

Thanks once again.

Signature

I may not know VBA inside out, but from the outside I am looking in.
Dylan Moran - Melbourne Australia

> Sorry, Dylan, step 5 needs modifiying because your date field has time
> values.
[quoted text clipped - 56 lines]
> >> date,
> >> and essentially ignores the DD:MM:YY part of the date.

Reply to this Message