Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Force a default value in MS Access

Posted on 2014-04-09
4
Medium Priority
?
2,413 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--
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 48

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 48

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 39

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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

721 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