Summing in a Query

G

Guest

Howdy from Oklahoma!!

I am new to ACCESS and needing help with a Query.

I am having a problem with a query that is performing an addition function
of 4 other fields. As in Sum1: Q1 + Q2 + Q3 + Q4. (where each Q# is an
alias for a different QTY value from the table.)

It works fine except when one of the Q's does not have a value and then the
Sum1 is blank.

I have tried using an IIF statement as in Q2: IIf([QTY2] = Null,0,[QTY2])
in the query to identify each alias Q# to make sure the fact that if the QTY#
value is null that a "0" is the value for the Q# field. but still the Sum1 is
blank when the query is ran.

Any ideas would be greatly appreciated. AND AS ALWAYS, THANKS IN ADVANCE!!!!
 
W

Wolfgang Kais

Howdy from Hamburg.

Chip said:
I am having a problem with a query that is performing an addition
function of 4 other fields. As in Sum1: Q1 + Q2 + Q3 + Q4.
(where each Q# is an alias for a different QTY value from the table.)

It works fine except when one of the Q's does not have a value and
then the Sum1 is blank.

Try Sum1: Nz(Q1,0) + Nz(Q2,0) + Nz(Q3,0) + Nz(Q4,0)
 
G

Guest

Hi Chip,

You got the answer from Wolfgang; this an explaination as to why the IIF()
didn't work the way you thought it would.
I have tried using an IIF statement as in Q2: IIf([QTY2] = Null,0,[QTY2])

You cannot do a test for NULL like this: [QTY2] = Null. This will *always*
be False.
If you tried NULL = NULL the result will also be False.

NULL is an unknown state - can two unknows be equal?

But you can use: IIF( IsNull( [QTY2] ) , 0, [QTY2])

If you want to test if something is null, use the IsNull() function.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Chip said:
Howdy from Oklahoma!!

I am new to ACCESS and needing help with a Query.

I am having a problem with a query that is performing an addition function
of 4 other fields. As in Sum1: Q1 + Q2 + Q3 + Q4. (where each Q# is an
alias for a different QTY value from the table.)

It works fine except when one of the Q's does not have a value and then the
Sum1 is blank.

I have tried using an IIF statement as in Q2: IIf([QTY2] = Null,0,[QTY2])
in the query to identify each alias Q# to make sure the fact that if the QTY#
value is null that a "0" is the value for the Q# field. but still the Sum1 is
blank when the query is ran.

Any ideas would be greatly appreciated. AND AS ALWAYS, THANKS IN ADVANCE!!!!
 
G

Guest

AWESOME!!

Makes since now,

THANKS A MILLION
Chip

SteveS said:
Hi Chip,

You got the answer from Wolfgang; this an explaination as to why the IIF()
didn't work the way you thought it would.
I have tried using an IIF statement as in Q2: IIf([QTY2] = Null,0,[QTY2])

You cannot do a test for NULL like this: [QTY2] = Null. This will *always*
be False.
If you tried NULL = NULL the result will also be False.

NULL is an unknown state - can two unknows be equal?

But you can use: IIF( IsNull( [QTY2] ) , 0, [QTY2])

If you want to test if something is null, use the IsNull() function.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Chip said:
Howdy from Oklahoma!!

I am new to ACCESS and needing help with a Query.

I am having a problem with a query that is performing an addition function
of 4 other fields. As in Sum1: Q1 + Q2 + Q3 + Q4. (where each Q# is an
alias for a different QTY value from the table.)

It works fine except when one of the Q's does not have a value and then the
Sum1 is blank.

I have tried using an IIF statement as in Q2: IIf([QTY2] = Null,0,[QTY2])
in the query to identify each alias Q# to make sure the fact that if the QTY#
value is null that a "0" is the value for the Q# field. but still the Sum1 is
blank when the query is ran.

Any ideas would be greatly appreciated. AND AS ALWAYS, THANKS IN ADVANCE!!!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

query error 12
Summing Problem 2
IIF returning text value 6
UNION Query problem 4
summing a query 3
Adding fields working for some records, not all 3
calculated field in query 11
error -3087 insert query 1

Top