Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2006
    Posts
    7

    Question Unanswered: Update Nulls across Multiple Fields

    I'm using Access 2000 and I would like to create an update query that will change any table records where one of the fields is null to 0(zero) , without having to create a query for each field. In other words one record will have a field that is null and others that aren't. I want to only update the null fields to 0 and leave the non null fields intact. Is there a way to do this without inadvertantly updateing fields that aren't null ?

    Thanks in advance

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    nz(<yourField>,0)
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why change?
    null isnt the same as 0
    0 means the value for that column is 0, nullmeans its unknown or unspecified.

    this makes a huge discrepancy if you arre doing statictical analysis on datasets containing NULLS or 0

    Im just wondering if the real issue is presentation?
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2006
    Posts
    7
    The fields that contain null are later used in calculations that are made in a query.
    It seems if null is the value the formulas don’t calculate correctly. The formulas in the query use the IIF function here is one example of a formula Expr1: IIf([MV LEFT 2]>=1 & [MV LEFT 2]<=[MV BRK PT2],[MV LEFT 2],0)

    Not sure if that helps or confuses matters

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    right!
    keep the nulls in the table - you never know when they might be useful

    in your query replace thisField with nz(thisField, 0) anywhere you might have nulls (check the table definition)

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Jul 2006
    Posts
    108
    try this:

    IIf(nz([MV LEFT 2],0)>=1 & nz([MV LEFT 2],0)<=nz([MV BRK PT2],0),nz([MV LEFT 2],0),0)

    assumes that any of the values can be null

Posting Permissions

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