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

    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
    The Bottom of The Barrel
    Provided Answers: 1
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Nov 2004
    out on a limb
    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
    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
    Préverenges, Switzerland
    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)

    currently using SS 2008R2

  6. #6
    Join Date
    Jul 2006
    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