When calls are being made

[Guest]

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.

 

[Allen Browne]

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

 

[Allen Browne]

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

The criteria should be:

= [StartDate] And < ([EndDate] + 1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Guest]

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.

--
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.

The criteria should be:

= [StartDate] And < ([EndDate] + 1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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