Time Return Query

[bacarat]

Hi Everyone,
I have a database that I enter time for in the form of military time, I would like to make a query to count the number of entrys in the PM,AM, And NIGHT. For instance if a record has a time of anywhere between 700 or 2299 I would like it to be in the AM catagory, then I would like to total up the number of AM records. Any help would be great. Thanks.

AM=700-1399 PM=1400-2299 NIGHT=2300-699

 

[RichO]

You can use nested IIf expressions to convert the military time settings into the 3 categories:

IIf([MyTimeField]>2259 Or [MyTimeField]<700, "NIGHT", IIf([MyTimeField]>659 And [MyTimeField]<1400, "AM", IIf([MyTimeField]>1399 And [MyTimeField]<2300, "PM", "")))

Or you can create 3 separate fields in a query, one for each category:

AM: IIf([MyTimeField]>659 And [MyTimeField]<1400,1,0)
PM: IIf([MyTimeField]>1399 And [MyTimeField]<2300,1,0)
NIGHT: IIf([MyTimeField]>2259 Or [MyTimeField]<700,1,0)

The result for each entry in each of these fields will be 1 or 0 which will allow you to total each category separately.

 

[bacarat]

Thanks, works great!