Results 1 to 4 of 4

Thread: Access Riddle

  1. #1
    Join Date
    Oct 2005
    Posts
    4

    Unanswered: Access Riddle

    What number is neither >= 1, nor <= 1 ?

    Here's my code:

    DoCmd.RunSQL "UPDATE test_tab SET NumAvailable = 9 WHERE numavailable >= 1"
    DoCmd.RunSQL "UPDATE test_tab SET NumAvailable = 0 WHERE numavailable <= 1"

    This results in a total of 35 of the 396 records to be updated. The datatype of the field is 'Number'.

    Hint: the test_tab table is populated using a select * into statement:

    DoCmd.RunSQL "SELECT * INTO test_tab FROM EA2"

    EA2 is a LEFT JOIN query, and the 361 records which were not updated resulted from there being elements in the left table, but not in the right one(hence, they appear to be blank when i view them in datasheet view).

    I am using Access 2000 and need to change the NumAvailable field to 0 for all of those 361 records, while leaving the other 35 untouched.
    (ie, UPDATE test_tab SET NumAvailable=0 WHERE ??? ???)

    What i have tried:
    ??? ??? = NumAvailable = '' <- doesnt work cause the datatype is number
    ??? ??? = NumAvailable < 1 <- doesnt work because they arent <= 1

    I appreciate any help you can give.

    Thanks,
    Pete Lloyd

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Perhaps WHERE (NumAvailable Is Null);

    Clarification: NumAvailable is a Number BUT that is not it's TYPE ... Is it a Double, Single, Integer, Long, or Byte?
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    May 2005
    Posts
    119
    [QUOTE=PeteLloyd]What number is neither >= 1, nor <= 1 ?

    Here's my code:

    DoCmd.RunSQL "UPDATE test_tab SET NumAvailable = 9 WHERE numavailable >= 1"



    Pete: You could try using the NZ function, Nz ( variant, [ value_if_null ] ). It converts NULL values to 0 (or another value if you so desire).

    DoCmd.RunSQL "UPDATE test_tab SET NumAvailable = 9 WHERE
    nz(numavailable,0) >= 1"

    I'm not sure if it would be better to use it here or in your append query - 6 of one, half-dozen of the other I guess.

    Hope this helps!
    Krista

  4. #4
    Join Date
    Oct 2005
    Posts
    4
    Looks like Null is the answer. I didnt realize a non-string could be null.

    Thanks.

    -Pete

Posting Permissions

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