Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2005
    Posts
    7

    Unanswered: Converting field with empty record to zero

    I have a query and one of the fields will sometimes have an empty or null record. I would like it to display "0" when this occurs to keep calcuations from reading ERROR. Any tips? I've hear of the Nz function if that works. Thanks for your help

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sounds like you already have the answer: the Nz() function.
    Paul

  3. #3
    Join Date
    Sep 2005
    Posts
    7
    I've tried the Nz function and it just skips over the records that are empty rather than including them in the data set with a zero. Here is my expression:

    Nz([Unredeemed ML - Step 2!SumOfDET_AMT])

    When the field listed has an empty record my running total calculation within the query reads #Error. Do I need the Nz function within the running total expression? Or should the above provide zero and the running total will calculate correctly? Thanks for your help.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Is that one long field or an attempted formula subtracting one from another? You need to stop using spaces and symbols ("!") in your object names. If that's a formula, try

    Nz([Unredeemed ML]) - Nz([Step 2!SumOfDET_AMT])

    I suppose the answer to your question is that the Nz function should be used at the lowest level the null could occur at, so that subsequent formulas will work properly. A sample db would help immensely.
    Paul

  5. #5
    Join Date
    Sep 2005
    Posts
    7
    Sorry for the confusion. [The Unredeemed ML - Step 2!SumOfDET_AMT] refers to the table and field within the table. I've tried using the Nz function to create a new field that will have the 0 where the original field has an empty record. This only seems to work when I use "Totals" within the query. The problem is when I use "Totals" my running total DSum function won't work anymore. Here is my Dsum expression. Tables have the same field names so I need to specify which table some fields come from

    Unredeemed Cumul: DSum("SumOfDET_AMT","Unredeemed ML - Step 2","[Unredeemed ML - Step 2!ORD_MONTH] <= " & [Unredeemed ML - Step 2!ORD_MONTH] & " ")

    I've just done my own simple query and I can get both the DSum function and the Nz to work. Also the DSum function read the empty record as a zero and totals in the Dsum function correctly. Could there be a setting or something that is wrong in my original query that's not working? Thanks I appreciate your help.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Can you post a sample db?
    Paul

  7. #7
    Join Date
    Sep 2005
    Posts
    7
    How do you do that?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Zip it and click Manage Attachments.

    Something Like:

    Code:
     
    Iif([MyCol] = "", "0", NZ(MyCol, "0")
    BTW - if you are using your field in calculations the field should be numeric not text. This would get rid of the zero length prob and will prevent non numeric chars too. Simple rule of thumb -- if adding it up has meaning, make the field numeric.

    Failing that I would use the IsNumeric function to make sure you don't try to divide 12 by "Nothing purchased"

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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