Multiple "Where" criteria for call arrivals

[cft]

I have two tables: one that has (in part) call data from calls to a call center. The data includes the initiating (or calling) number/Start Date of the call/ Start Time/End Time/Duration, as well as City and State (abbreviation) of origin.
I also have a table that includes time zones by state (abbreviation) [ie ME (Maine)= Timezone 1 and CA (California) = Timezone 4 in the US.]
I am trying to parse the data by developing a query to break the call arrival into 1/2-hour increments. I.e. I want to see the total number of calls arriving by sum of calls by state (time zone) each 1/2-hr.
When I set the criteria statement as "Start Time" Where (and in the criteria section) >#1:00:00 PM# And <#1:30:00 PM# the query fires off providing the required data. However, if I add the addtional OR statement >#3:00:00 PM# And <#3:30:00 PM# for instance, the query dies.
I need to establish a parsing mechanism that, in a single query, takes the data from any date range and breaks it into 1/2-hour increments to determine where the calls are coming in from and how the pattern changes during the day (or over time). Thank you, in advance, for any assistance you may be able to provide.

 

[gromit]

Hi -

My first guess is that you have some sort of syntax error or need parentheses somewhere in your WHERE statement. I always find in very helpful to break queries apart and test each piece and then combine once I know that the individual parts work.

A slightly different approach might work for you better than a bunch of WHERE statements. Convert the time part of the Start Time to a numeric value and round to the nearest multiple of 0.5

E.g.

DecimalTime = DatePart ("h",StartTime) + Int(DatePart("n",[StartTime])/30)*0.5

[Check to see that this rounds properly].

The advantage here, aside from eliminating bunches of WHERE statements, is that you can use GROUP BY in your queries, and also make charts.

HTH,

- g