Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Adding a null value in access table

Posted on 2014-07-16
Medium Priority
Last Modified: 2014-07-19
I have two columns in Access. I would like to sum fields from these two columns into a new column but I have some NULL values in some of the fields.

How can I do this?

Question by:vpopper
LVL 21

Expert Comment

by:Randy Poole
ID: 40199951
for those colums use the isnull with IIF as

Open in new window

LVL 85
ID: 40199961
Or just use Nz. If you're doing this in a query, for example:

SELECT Col1, Col2, Nz(Col1,0) + Nz(Col2, 0) AS SumOfCols

Author Comment

ID: 40199984
Total - All Credits: =NZ[Supplier Credit] + NZ[SS Credit]

This is my ACCESS Query - using NZ but this is not working???

LVL 49

Expert Comment

by:Dale Fye
ID: 40199992
The NZ( ) function must have opening and closing ()

Total - All Credits: NZ([Supplier Credit], 0) + NZ([SS Credit], 0)

BTW,  it is a bit confusing that you are labeling this column as "Total - All Credits" and are doing a sum in the calculation.
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 40199996
You must enclose Nz with parentheses:

Total - All Credits: =NZ([Supplier Credit],0) + NZ([SS Credit],0)

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

577 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