Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jun 2006
    Posts
    25

    Unanswered: <> does not work, but = does

    I built a temp table(KEEPER) to from the original (V1A) to null the duplicate numbers with a lesser value.
    (IIf([v1a]![OEM DCs]<>[KEEPER]![MaxOfOEM DCs],9999999,[KEEPER]![Seq])

    When I use this, nothing gets updated. I've also used [v1a]![OEM DCs]<[KEEPER]![MaxOfOEM DCs] Now when I use = it works fine.

    Has anybody ever had this problem?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Are there NULL values involved here by any chance?
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2006
    Posts
    25
    I actually null any null values, just to make sure, but no, no null values. I hope I am understanding your question.

  4. #4
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    cerv008 share a copy of your db if possible to see what is the problem
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

  5. #5
    Join Date
    Jun 2006
    Posts
    25

    Here is what I am working on.

    One query requests to null a value and the other one requests it to "999999".

    Thanks in advance for your help.
    Attached Files Attached Files

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    cervantes - do you know what the answer to this boolean expression is:

    ?NULL = 0

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ooh, ooh I know, I know, pick me
    George
    Home | Blog

  8. #8
    Join Date
    Jun 2006
    Posts
    25
    I know what null means. What I was trying to state was I've nulled anything that had no visible characters in there. It was the context I was trying to figure out in the question. In the same database, there was a "make table" query I had that created a column that did not previously exist (column A:"") and the next query didn't recognize it as null, so I had to null it out just to show that the information in the newly created column had a null value.

    Maybe I can't convey myself in a savvy programming manner, so please excuse me for trying to explain my issues.

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What they're asking is if you know how Null behaves in comparison operations.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Jun 2006
    Posts
    25
    If it's related to this dilemma that I'm having, then I'm stumped and I obviously need a refresher. Does anybody know the answer to this riddle and if so, please throw me a bone? I thought this is what a forum's purpose was.

    I don't seem to understand how I can't use = and not <>. That's it. As for the null inquiry, I apologize for attempting to answer. I tried to explain, as well as post my db.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    "NULL = NULL" will return false
    "NULL <> NULL" will return false
    "NULL IS NULL" will return true
    George
    Home | Blog

  12. #12
    Join Date
    Jun 2006
    Posts
    25
    Ok, maybe we might have strayed off the wrong path. My issue is not nulling null items.

    Here is my original problem in sql.

    UPDATE v1a INNER JOIN KEEPER ON v1a.Seq = KEEPER.Seq SET v1a.Seq = IIf(v1a![OEM DCs]<>KEEPER![MaxOfOEM DCs],999999,KEEPER!Seq);

    ---------no updates occur---------

    Now, even if I changed it to:

    UPDATE v1a INNER JOIN KEEPER ON v1a.Seq = KEEPER.Seq SET v1a.Seq = Null
    WHERE ((([V1A]![OEM DCs])<>[KEEPER]![MaxOfOEM DCs]));


    Still doesn't work.

    ---------no updates occur---------


    but if it's changed to:

    UPDATE v1a INNER JOIN KEEPER ON v1a.Seq = KEEPER.Seq SET v1a.Seq = Null
    WHERE ((([V1A]![OEM DCs])=[KEEPER]![MaxOfOEM DCs]));


    Works fine.
    ---------updates occur---------


    I am not trying to null out null items. I am simply trying to match my 'seq' to the temp table 'seq' and comparing the 'dcs' to the 'maxdc's' and if 'dcs' don't match maxdc's null out 'seq'.

    Thanks George for your input, but I think everybody is stuck on the answer I gave you on your initial question.

    Criteria:
    Table1!Seq matches Table2!Seq
    Table1!dcs doesn't match Table2!dcs
    Once both are true, null Table1!Seq

    but nothing gets updated and I know there are multiple updates that should have updated.

  13. #13
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    Cervantes, the problem is when you have null in one side of equation and a number in the other side (say null<>2) the resualt is false as the whole equation is wrong because null isn't a number to be comapred with numbers so you shall put another iif statement to convert all null items to 0 during calculations to make the equation to function correctly as bellow:
    Code:
    IIf(IIf(IsNull([v1a]![OEM DCs]),0,[v1a]![OEM DCs])<>[KEEPER]![MaxOfOEM DCs],Null,[KEEPER]![Seq])
    Cheers
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

  14. #14
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Nulls are such fun!

    Try to think of Null as "Unknown". It's not comparable to anything. EG is something you know nothing about equal to this or that... you can't know because you don't know what it is.

    Null is NOT zero.
    Null is NOT Nothing.
    Null is NOT a zero length string ("").
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    "NULL = NULL" will return false
    "NULL <> NULL" will return false
    "NULL IS NULL" will return true
    Nooooooo!


    Cervantes - I wasn't holding back the bone - just asking a question.

    Code:
    NULL = 'something' returns NULL
    NULL <> 0 returns NULL
    NULL = NULL returns NULL
    NULL IS NULL returns TRUE
    0 IS NULL returns False
    Basically bollean logic breaks down in SQL when NULLS are involved. A NULL is an unknown value. Hence comparing it to other values results in an unknown return. If you substitute the word unknown for NULL above it makes lots of sense. (Just read StarTrekker's post - same point )

    This is what is happening in your expression. You need to handle comparing known values to unknown values (i.e. NULLS).

Posting Permissions

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