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
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.
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.
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.
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"