Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update odd # to A and even # to B (2000)

    I have a database that has over 17,000 records in it. Because of the way the data was received I need to assign A to all of the odd numbers and B to all of the even number records. I know how to do Update queries. But I not sure how to write the criteria in this situation. Any ideas or help would be appreciated. Thank you. Fay

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Update odd # to A and even # to B (2000)

    First thing to do is backup your database!

    I'd do it in 2 passes. Use a selection criteria to select the odd or even values. This should return True for even numbers:

    ( CLng(someField/2) *2 = SomeField)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Star Lounger
    Join Date
    Dec 2001
    Location
    Birmingham, Alabama USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update odd # to A and even # to B (2000)

    A simpler way would be to use the Update Query. In the "Update To" section of the field you want the "A" or "B" .... use the following example:

    IIf([YourNumber] Mod 2=0,"B","A")

    "YourNumber" should be the name of the field that contains the numbers to evaluate as Odd or Even.

    HTH
    RDH
    Ricky Hicks
    Microsoft MVP
    Birmingham, Alabama USA

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update odd # to A and even # to B (2000)

    It worked like a champ. Thank you. Can you explain the code a little more. The Mod2=0,"B","A" part.

    Once again you guys come through again. Fay

  5. #5
    Star Lounger
    Join Date
    Dec 2001
    Location
    Birmingham, Alabama USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update odd # to A and even # to B (2000)

    Mod 2 .... means divide the number by 2 and only return the remainder.

    So if you think about it .... any whole number that can be divided by 2 equally with no remainder is an "Even" number. The rest will be "Odd".

    Hope this explains what the expression does.

    RDH
    Ricky Hicks
    Microsoft MVP
    Birmingham, Alabama USA

Posting Permissions

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