Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Force a default value in MS Access

Posted on 2014-04-09
4
Medium Priority
?
2,541 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 49

Accepted Solution

by:
Dale Fye earned 840 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 49

Assisted Solution

by:Dale Fye
Dale Fye earned 840 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 40

Assisted Solution

by:PatHartman
PatHartman earned 240 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
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 …
Suggested Courses

876 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