How do you add four numbers in an Access query?

[Guest]

Trying to add four values(four different fields) in a query. When there are
values in all four fields the query works. If a field is empty, the query
does not work.

 

[Joseph Meehan]

Trying to add four values(four different fields) in a query. When
there are values in all four fields the query works. If a field is
empty, the query does not work.

I am not at all sure what you are saying.

Do you mean you want to add four fields to a query? Are they all from
the same record source?

Do you mean you want to add something to a query that is not coming from
an outside record source?

 

[Guest]

Use the Nz function to convert a null to zero. For example,

TotalValue: =Nz([Field1],0) + Nz([Field2],0) + Nz([Field3],0) + Nz([Field4],0)

where Field1, Field2, Field3 and Field4 are the names of the fields
involved. You need to make the appropriate substitutions for these fieldnames.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Douglas J Steele]

Instead of

Field1 + Field2 + Field3 + FIeld4

you need to use

Nz(Field1,0) + Nz(Field2,0) + Nz(Field3,0) + Nz(FIeld4,0)

 

[Guest]

Place a 0 (zero) where it is now blank.

You can have this done automatically by going into your table, setting the
Default Value to 0 (zero), and then set Required to yes. This will force a
zero to be displayed.

 

[George Nicholson]

I assume you have something in your query like "MyCalculatedField = [Field1]
+ [Field2] + [Field3] + [Field4]". This will work fine unless one of the
fields happens to be Null. A single Null will make the entire calculation
return Null. Try the following instead:

MyCalculatedField = nz([Field1],0) + nz([Field2],0) + nz([Field3],0) +
nz([Field4],0)
Nz() "substitutes" the specified value (in this case zero) for Null when it
is encountered. You are only making a substitution in your calculation, not
your data.


HTH,

 

[Guest]

mnature,

A default value of zero for numeric fields is not always appropriate. For
example, if you have a database that tracks daily temperatures for various
cities, and you don't have a given city's temperature value available, it's
much better to leave the field as null (unknown) rather than entering zero.
If this happened for Anchorage, Alaska, who would be the wiser--certainly
zero would seem like a reasonable entry for this city.

It's generally better to use the Nz function to convert nulls on-the-fly, as
needed, rather than force a value.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Good point. I sometimes concentrate too much on "pure" numbers, and forget
that they are actually representing something in reality.