Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Expression in Query (2000)

    Try:

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

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •