Solved

Null Field Equals Zero In Access

Posted on 2007-12-03
4
416 Views
Last Modified: 2012-06-21
I have an Access table with 4 columns  I am using an expression to sum two of the columns to update my totals columns.  However  I need to populate the null fields of one of the columns that is being summed with zero so the math will work.  How can that be done?  Thanks.
0
Comment
Question by:submarinerssbn731
  • 2
4 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 50 total points
ID: 20396328
Nz(YourColumnNameGoesHere, 0) will convert any null values to zero.

0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20396345
SELECT Nz(Field1,0), Nz(Field2,0), Nz(Field3,0), Nz(Field4,0) FROM tblMyTable;
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20396357
Smile, looks like Jim beat me to the post.
0
 

Author Closing Comment

by:submarinerssbn731
ID: 31412359
It did convert the null fields to zero but the query only returned the records with null fields.  I want to return all rows when I run the query.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

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…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

12 Experts available now in Live!

Get 1:1 Help Now