Solved

Force a default value in MS Access

Posted on 2014-04-09
4
1,992 Views
Last Modified: 2014-04-09
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?
0
Comment
Question by:--TripWire--
  • 2
4 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 210 total points
ID: 39988628
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
 

Author Comment

by:--TripWire--
ID: 39988645
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
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 210 total points
ID: 39988748
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
 
LVL 35

Assisted Solution

by:PatHartman
PatHartman earned 60 total points
ID: 39988761
"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

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question