Combine IIf with NZ to get null for zero calculated sum?

[Ann Scharpf]

Is there any way to combine and IIf() with the NZ function so that if the
RESULT of the calculation is zero, I can get a null? I need the NZ() to get
the query to calculate properly when either of my variables is a null. But I
am producing an output that's being used to identify data discrepancies for
over 100 people for 26 pay periods. It would be a lot easier to spot the
errors if the "good" data was just blank space rather than a lot of zeroes.

I'd appreciate any ideas you can give me. Thanks.

 

[KARL DEWEY]

Sure you can.
New_Field: IIF(Nz([field1],0)+Nz([field2],0)+Nz([field3],0) = 0, Null,
Nz([field1],0)+Nz([field2],0)+Nz([field3],0))

 

[Ann Scharpf]

It's so OBVIOUS when you type it up for me! I can't believe I didn't think
of that. Thanks so much.

One more question if you don't mind. I'm pretty much self taught in Access
and I probably have a lot of bad habits just because I've discovered things
that work but aren't the recommended method. I didn't know you could use
"Null" in a formula like that. I have always used "" which has seemed to
work fine. Is there a difference that isn't apparent to me between the two?

Thanks again for helping me out. This will make the query output so much
easier to work with.

--
Ann Scharpf

Sure you can.
New_Field: IIF(Nz([field1],0)+Nz([field2],0)+Nz([field3],0) = 0, Null,
Nz([field1],0)+Nz([field2],0)+Nz([field3],0))


--
KARL DEWEY
Build a little - Test a little

Is there any way to combine and IIf() with the NZ function so that if the
RESULT of the calculation is zero, I can get a null? I need the NZ() to get
the query to calculate properly when either of my variables is a null. But I
am producing an output that's being used to identify data discrepancies for
over 100 people for 26 pay periods. It would be a lot easier to spot the
errors if the "good" data was just blank space rather than a lot of zeroes.

I'd appreciate any ideas you can give me. Thanks.

 

[KARL DEWEY]

I too am self taught.
"" is a zero lenght string. Null is absolutely nothing.
A date field can be null. You can not replace a date with "".

Others can explain it a lot more in detail than I.

--
KARL DEWEY
Build a little - Test a little

It's so OBVIOUS when you type it up for me! I can't believe I didn't think
of that. Thanks so much.

One more question if you don't mind. I'm pretty much self taught in Access
and I probably have a lot of bad habits just because I've discovered things
that work but aren't the recommended method. I didn't know you could use
"Null" in a formula like that. I have always used "" which has seemed to
work fine. Is there a difference that isn't apparent to me between the two?

Thanks again for helping me out. This will make the query output so much
easier to work with.

--
Ann Scharpf

Sure you can.
New_Field: IIF(Nz([field1],0)+Nz([field2],0)+Nz([field3],0) = 0, Null,
Nz([field1],0)+Nz([field2],0)+Nz([field3],0))


--
KARL DEWEY
Build a little - Test a little

Is there any way to combine and IIf() with the NZ function so that if the
RESULT of the calculation is zero, I can get a null? I need the NZ() to get
the query to calculate properly when either of my variables is a null. But I
am producing an output that's being used to identify data discrepancies for
over 100 people for 26 pay periods. It would be a lot easier to spot the
errors if the "good" data was just blank space rather than a lot of zeroes.

I'd appreciate any ideas you can give me. Thanks.