TIME CROSSTAB - Grouping Records by Morning/Afternoon/Evening

[Gordon]

I have created a query which gives me the date and time of events. Now I want to create a crosstab query which summarizes those events according to whether they occurred in the morning, afternoon or evening.

This would be along the lines:
00:01 - 12:00 -- Morning
12:01 - 1800 - Afternoon
18:01 - 24:00 - Evening

My inital thought was to add a field in the base query which referenced the "time" field, and used an "if," statement.... along the lines of:

morning_Afternoon: IF([TIME]<=12,"Morning","Afternoon").

I get an error message when I try this, which says I have an "undefined IF," expression.

If I can do this through queries/macros and/or reports (rather than VBA) that would be my preference, but I'm open to any ideas!

Thanks for any help!

Gordon

 

[tjs206]

Your IF statement is set up looking like it should be an IMMEDIATE IF statement. This might explain the undefined IF error that you are receiving.

Try, morning_Afternoon: IIF([TIME]<=12,"Morning","Afternoon").

Hope this helps.

 

[Gordon]

More on Time (Morning/Afternoon/Evening) for Crosstab

Thanks so much for the suggestion.

I tried putting morning_Afternoon: IIf([TIME]<=12,"Morning","Afternoon") into the field area, and I got the response of "Morning," for all of the times listed, except for one where there was no time listed.

Ideally I would like times to come out as

Morning
Afternoon
Evening

Anyone have an idea as to how to accomplish that?

Thanks.......

Gordon

 

[EMP]

What is the data type of the [Time] field - a text field or a date/time field?

 

[Gordon]

What is the data type of the [Time] field - a text field or a date/time field?

The field in the underlying table is date/time. Format is "Short Time," and Input Mask is "00:00;0;_"

Thanks for the help!

Gordon

 

[EMP]

The clarification is needed because a date/time field can't have 24:00

Try this:-

IIf(IsNull([Time]),Null, IIf(Format([Time],"hh:nn:ss")<="12:00:00","Morning", IIf(Format([Time],"hh:nn:ss")<="18:00:00","Afternoon","Evening")))

 

[Gordon]

Time - Morning/Afternoon/Evening --- Thanks!!!

The clarification is needed because a date/time field can't have 24:00

Try this:-

IIf(IsNull([Time]),Null, IIf(Format([Time],"hh:nn:ss")<="12:00:00","Morning", IIf(Format([Time],"hh:nn:ss")<="18:00:00","Afternoon","Evening")))

---------------------------------------------------------------------
Thanks so much!! That solved the problem!!

Gordon

gbaillie@openspace.org