Nz puzzle

  • Thread starter Thread starter JohnLute
  • Start date Start date
J

JohnLute

I posted this in the Forms Coding Area however it made sens to bring it here,
too.

Here's my design. Imagine reports instead of forms:

Main Form [frmFGPackConfigurations]
Subform [sfrmFGPackConfigsPhysicalAtts]
Subform of sfrmFGPackConfigsPhysicalAtts [sfrmFGPKConfigsPVs]

The following is in the unbound control
[sfrmFGPackConfigsPhysicalAtts].[SUCU]:
=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin]/[SUcuin],"1.00")

This returns as expected.

The following is in the unbound control
[sfrmFGPackConfigsPhysicalAtts].[SUcuin2]:
=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin],[SUcuin])

This does NOT return as expected. It displays the value in [SumPVTotalcuin]
regardless if there's a value in [SUcuin]. When [SUcuin] is Null then it
displays Null when it should be displaying [SumPVTotalcuin].

Any help you might have is greatly appreciated. This one just has me turning
in circles.

Thanks!!!
 
JohnLute said:
I posted this in the Forms Coding Area however it made sens to bring it here,
too.

Here's my design. Imagine reports instead of forms:

Main Form [frmFGPackConfigurations]
Subform [sfrmFGPackConfigsPhysicalAtts]
Subform of sfrmFGPackConfigsPhysicalAtts [sfrmFGPKConfigsPVs]

The following is in the unbound control
[sfrmFGPackConfigsPhysicalAtts].[SUCU]:
=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin]/[SUcuin],"1.00")

This returns as expected.

The following is in the unbound control
[sfrmFGPackConfigsPhysicalAtts].[SUcuin2]:
=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin],[SUcuin])

This does NOT return as expected. It displays the value in [SumPVTotalcuin]
regardless if there's a value in [SUcuin]. When [SUcuin] is Null then it
displays Null when it should be displaying [SumPVTotalcuin].
Click to expand...


I tried to explain this last week in another thread. Maybe
you didn't see my response?

First, your use of Nz is inappropriate. For instance, if
SUcuin has a value of 0, your expression will treat it the
same as a null value. If you want to check for Null, use
the IsNull function.

I think(?) you want something more like:

=IIf(IsNull([SUcuin],[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin],[SUcuin])

But that presumes that SUcuin really is Null when you think
it is. Maybe SUcuin has some other value that appears
blank, but is not really Null (e,g, space or zero length
string?).
 
Thanks, Marshall! I resolved the problem with your help. As always - I
appreciate it!
--
www.Marzetti.com


Marshall Barton said:
JohnLute said:
I posted this in the Forms Coding Area however it made sens to bring it here,
too.

Here's my design. Imagine reports instead of forms:

Main Form [frmFGPackConfigurations]
Subform [sfrmFGPackConfigsPhysicalAtts]
Subform of sfrmFGPackConfigsPhysicalAtts [sfrmFGPKConfigsPVs]

The following is in the unbound control
[sfrmFGPackConfigsPhysicalAtts].[SUCU]:
=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin]/[SUcuin],"1.00")

This returns as expected.

The following is in the unbound control
[sfrmFGPackConfigsPhysicalAtts].[SUcuin2]:
=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin],[SUcuin])

This does NOT return as expected. It displays the value in [SumPVTotalcuin]
regardless if there's a value in [SUcuin]. When [SUcuin] is Null then it
displays Null when it should be displaying [SumPVTotalcuin].
Click to expand...


I tried to explain this last week in another thread. Maybe
you didn't see my response?

First, your use of Nz is inappropriate. For instance, if
SUcuin has a value of 0, your expression will treat it the
same as a null value. If you want to check for Null, use
the IsNull function.

I think(?) you want something more like:

=IIf(IsNull([SUcuin],[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin],[SUcuin])

But that presumes that SUcuin really is Null when you think
it is. Maybe SUcuin has some other value that appears
blank, but is not really Null (e,g, space or zero length
string?).
Click to expand...
 
Back
Top