Solved

Force a default value in MS Access

Posted on 2014-04-09
4
1,834 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
Comment Utility
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--
Comment Utility
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
Comment Utility
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 34

Assisted Solution

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

771 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now