Using the Avg() Function on specific dates in reports

Question

Hello,

I have a report that lists values by time of day, grouped by day

I want to AVG the values for morning, afternoon, and evening.

The records have a date and time stamp in them 

I have tried several different things but always get a syntax error.

here is an example of what I have tried:

Avg([Copy Of qryEvening].Reading between -1+#6:00:00 PM# And [ReadingDate]+#1:00:00 AM# Or Between [ReadingDate]+#6:00:00 PM# And +1+#1:00:00 AM#) 

hopefully, this gives insight as to what I am trying to accomplish.

Answer 1

Did you use the Report wizard to create the report? Please still show us your SQL, And show us the Group/Sort pane

Answer 2

It isn't the query, it is the report, trying to group TOD, then I get mismatch error message, the Query works fine

Answer 3

Access uses error message "data type mismatch in criteria expression" when that is not the problem.

Post the complete SQL of the query.

Answer 4

When I try to group on "TOD" i receive an error message "data type mismatch in criteria expression"

Answer 5

Add a column to a query like:

TOD: IIF(TimeValue(datefield) BETWEEN #06:00 AM# AND #12:00 PM#,"Morning",IIF(TimeValue(datefield) BETWEEN #12:01 PM# AND #5:00 PM#, "Afternoon","Evening"))

Then group your report by TOD and add an AVG to the Grouping.