Force a default value in MS Access

I am running a MS Access 2010 DB, and I have a report that is supposed to perform a calculation on several different currency fields.  However, I keep getting blanks (in some records) in one of my fields, despite the fact that the default value is 0.  This is forcing the total column to display blanks, since it doesn't have all the values it needs to calculate the final result.

How do I force this field to display/store a value of 0 for every instance of a blank?
--TripWire--Asked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
are the fields strings or numbers?  a "blank" assumes a zero length string as opposed to a NULL which has no value.

if any of the fields in a numeric computation includes a NULL value, the entire value becomes NULL.  To ensure your calculations work, you need to force NULL values to zero using the NZ( ) function, like:

NZ([field1], 0) + NZ([Field2], 0)
0
 
--TripWire--Author Commented:
I'm not sure if we're on the same page.
I would assume that the values are blanks since the word NULL does not appear in the cell.

The report I'm generating will be adding thousands of values together.  Therefore doing a manual calculation would not be possible.

The field itself is formatted to be a currency value, with auto dp.
I want this field to retain all its current values, and where ever there are blanks, the blanks are replaced with 0 or $0.00 .
0
 
Dale FyeConnect With a Mentor Commented:
The word will not appear in the cell, and it is impossible to tell by looking at it whether it is NULL or a zero length string (blank).

so to add thousands of values together, are you writing a query that uses the SUM( ) aggregate function, or are you actually adding field values together in a query or in a VBA loop.

Either way, if you are summing multiple columns in a query, you would use:

SELECT VAL(NZ([Field1], 0)) + VAL(NZ([Field2], 0)) as [NewValue]
FROM yourTable

If, instead of that you are looping through a recordset, you will still need to use the NZ( )
function to transform potential NULL values to zero, like:

dblNewValue = 0
While not rs.eof

    dblNewValue = dblNewValue + NZ(rs!Field1, 0) + NZ(rs!Field2, 0)

    rs.movenext

Wend
0
 
PatHartmanConnect With a Mentor Commented:
"Empty" numeric fields are ALWAYS null.  There is no other possibility.  Access NEVER shows "null" for null values unless you format the field that way in a form or report.

"Empty" text fields can be null OR they can be ZLS (zero length strings) OR even really spaces.

The Nz() function is required when doing arithmetic with numeric fields if it is possible for any of them to be null.  When you perform a calculation and ANY of the operands is null, the result will be null.  So 39393 + null = null.  That means that changing the report display may not fix the problem.  In the previous example, you would want the report to show 39393 but it would show 0 if you simply display nulls as zero.

So, you need to take Dale's suggestion to modify your calculations to use the Nz() function appropriately.  Nz(fld1, 0) + Nz(fld2, 0) -- is appropriate.  Nz(fld1 + fld2, 0) is NOT.
0
All Courses

From novice to tech pro — start learning today.