Adding two columns that both contain zeros

[rynorem]

I have to add the row data in two columns together. I used

val([COLUMN 1])+val([COLUMN 2]) and it works unless one of the columns has a zero in it. For example if column 1 is 25 and column 2 is NULL it runs the query but puts a #ERROR in those rows. Please help.

 

[Jon K]

You can use the Nz() functions in the expression:-

Val(Nz([COLUMN 1]))+Val(Nz([COLUMN 2]))


If Column 1 and Column 2 are numeric fields, you don't need the Val():-

Nz([COLUMN 1])+Nz([COLUMN 2])
.

 

[tehNellie]

val([COLUMN 1])+val([COLUMN 2]) and it works unless one of the columns has a zero in it. For example if column 1 is 25 and column 2 is NULL it runs the query but puts a #ERROR in those rows. Please help.

At a risk of being anal about it, NULL is not equivalent to 0. In an ideal world, if you need to run calculations on a column you should ensure that it wont take NULL as a value. If you think of NULL as being basically equivalent to "I dont know" then any calculation on NULL will always return NULL (or an error) depending on what you are doing.

The NZ function mentioned by Jon allows you to substitute NULL to get around the problem if you must store NULL in the column (and there may well be valid reasons why you might).

 

[rynorem]

tehNellie- NULL Question

Is there somewhere in the Query Design view where I can say if there is a NULL make it a zero. John's way did bring up more rows but I noticed if column 1 is null and column 2 says 2 then the row doesn't show. Here's what I'm trying to do:

Table:Column 1
UIC(Dept)
S COUNT (CountofCivEmployee)

Table: Column 2
UIC (DEPT)
G COUNT (CountofGOVTEmployees)


I need a query that shows:
UIC and the 2 COUNT added together.

Here's the query in SQL:

SELECT [ASGN S].UIC, [ASGN S].COUNT AS [S COUNT], [ASGN G].COUNT AS [G COUNT], Val(Nz([G COUNT]))+Val(Nz([S COUNT])) AS Expr1
FROM [ASGN G] RIGHT JOIN [ASGN S] ON [ASGN G].UIC = [ASGN S].UIC
ORDER BY [ASGN S].UIC;


THanks

 

[Jon K]

I created two tables using the field names from your query and deliberately put some null values in the two tables.

I had no problem running your query in the attached database. The results returned were all correct:-

UIC	S COUNT	G COUNT	Expr1
a001	1	1	2
a002	2		2
a003		3	3
a004	4	4	8
a005	5		5
a006	6		6

Can you post a db with some sample data and your query (preferably in Access 2000 file format) for us to have a look at it?
.

 

[gemma-the-husky]

in the query design instead of a fieldname, put nz(fieldname,0) in the query data row. The only trouble is, is that now you wont be able to update that field directly, because it is now a calculated field

 

[boblarson]

The only trouble is, is that now you wont be able to update that field directly, because it is now a calculated field

Actually, the entire query becomes "not updateable" when you have any calculated fields in it.

 

[EMP]

put nz(fieldname,0) in the query data row

The 0 in the second argument returns a text field.

^

 

[Jon K]

The thread is becoming more and more interesting.

I noticed if column 1 is null and column 2 says 2 then the row doesn't show.

--------------------------------
I need a query that shows:
UIC and the 2 COUNT added together.

Here's the query in SQL:

SELECT [ASGN S].UIC, [ASGN S].COUNT AS [S COUNT], [ASGN G].COUNT AS [G COUNT], Val(Nz([G COUNT]))+Val(Nz([S COUNT])) AS Expr1
FROM [ASGN G] RIGHT JOIN [ASGN S] ON [ASGN G].UIC = [ASGN S].UIC
ORDER BY [ASGN S].UIC;

Obviously the poster meant the sum did not show when [G COUNT] was Null and [S COUNT] was 2. He/she was not worrying about displaying the Nulls in [S COUNT] or [G COUNT].

We can find out why it doesn't sum as soon as the poster posts the db.
.