473,324 Members | 2,567 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Fill fields with Zero for calculations to work

Bob
Hi all,

I have some additions that I need to do on some fields in some of my
queries but it seems that my fields don't always have a value in
them...

So for example, I want the result of ColA + ColB, but because ColA = 50
and ColB is NULL I get a null value in my formula. Previously I've
been doing a lot of: iif(ColB is null,0,ColB) prior to the calculation
but I'm thinking there must be an easier way to automatically fill
fields with a zero if they are null so that the calcs always work?

Bear in mind that these are not fields directly from tables, they have
been put through many queries..

Jan 24 '07 #1
3 1709

Check out the NZ( ) function..

Jan 24 '07 #2
To expand on Mary's reply:

If A = 10 and B = Null then

A + B will yield Null

but

Nz(A) + Nz(B) will yield 10

Mary wrote:
>Check out the NZ( ) function..
--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200701/1

Jan 24 '07 #3
Bob
Thanks guys.

On Jan 25, 2:21 am, "missinglinq via AccessMonster.com" <u28780@uwe>
wrote:
To expand on Mary's reply:

If A = 10 and B = Null then

A + B will yield Null

but

Nz(A) + Nz(B) will yield 10

Mary wrote:
Check out the NZ( ) function..--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2007...
Jan 25 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Joe C | last post by:
I'm a hobbiest, and made the forray into c++ from non-c type languages about a year ago. I was "cleaning up" some code I wrote to make it more "c++ like" and have a few questions. I'm comfortable...
4
by: Sherwood Botsford | last post by:
Table Markers ID (Primary Key) This&That PointClass (Combo box) Points Table PointClasses PointClass (primary key) Points (number) Description (Text)
18
by: TORQUE | last post by:
Hi, Im wondering if anyone can help me with a problem. I have a form with more than 50 unbound fields. Some of the fields will be blank from time to time. This seems to be where im having...
32
by: vonclausowitz | last post by:
Hi All, I have database with names on which I want to use the soundex option. So I have created two seperate fields for the Lastname and Firstname in which I save the Soundex version of a new...
1
by: moorebj | last post by:
I am having trouble writing a SELECT query. I have a "Customer" table with "Customer Name", "Street Address" and "Suburb, State, Post Code" fields. On a form I would like to be able to enter in...
3
by: Tonij (with a J) | last post by:
Hi all, I have an Access 2003 database that has been a work in progress for a while, it's basically a system inventory and issue tracking system. Recently I have added a seperate area for...
4
by: H.S. | last post by:
Hello, I am trying out a few methods with which to test of a given number is practically zero. as an example, does the following test correctly if a given number is zero within machine...
4
by: MichaelK | last post by:
Hello. I have all data already collected on the current page? I want to open another window with the form, fill the fields and submit that form. So basically the question is how can I fill all...
25
by: sarthur | last post by:
I am new to programming using VBA code. I am involved in developing a database. I created most of the tables and queries. Now I am at the complicated part of implementing some calculations, so I...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.