# Thread: Expression in Query (2000)

1. ## Expression in Query (2000)

I have a query that selects records between a selected date range. One of the fileds is Hour. I would like to add a field that indicates shift 1, 2, or 3. So I need an expression that will look at the hour and determine if it is between the hours of 8 to 16 for shift 1, 17 to 24 for 2nd shift, and 24 to 8 for 3rd. How do I add an logical expression to a query?

Thanks,
Bz

2. ## Re: Expression in Query (2000)

Try:

Shift: ([Hour]-24*([Hour]<8)) 8

3. ## Re: Expression in Query (2000)

Thanks hans... that returns a 1, 2, or 3 as wanted. Can you maybe explain how that works? What does the < do?

4. ## Re: Expression in Query (2000)

Shift: ([Hour]-24*([Hour]<8)) 8

The expression ([Hour]<8) is True if Hour is less than 8 (for example 1 or 7), and False if Hour is 8 or more (for example 8 or 13 or 22).
In VBA and in expressions in Access (which use VBA), True = -1 and False = 0. This value is multiplied with 24, so you get either -24 or 0.
This is subtracted from Hour. So in fact, if Hour is less than 8, 24 is added to it: 1 becomes 25 and 7 becomes 31, but 8 remains 8 and 22 remains 22.
The operator performs integer division, i.e. the quotient is truncated to a whole number, the fractional part is discarded. Here we divide by 8.

Hour = 7 becomes 31 and this divided by 8 results in 3.
Hour = 13 remains 13, and this divided by 8 results in 1.
Hour = 22 remains 22, and this divided by 8 results in 2.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•