IIf Nz

[JohnLute]

I'm having trouble with IIf Nz in an unbound control. Here's what I've got:
=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin],[SUcuin])

The way I need it to work is:
If [SUcuin] has a value then the unbound control needs to return this value.
If [SUcuin] is null then the unbound control needs to return the value in
[SumPVTotalcuin].

I believe I have it properly written however it behaves:
If [SUcuin] has a value then the unbound control returns the value in
[SumPVTotalcuin].
If [SUcuin] is null then the unbound control returns null.

Does anyone see what I'm doing wrong? Your help is greatly appreciated.

Thanks!

 

[Klatuu]

Simplify:
=Nz([SUcuin],sfrmFGPKConfigsPVs.Form!SumPVTotalcuin)

 

[John W. Vinson]

I'm having trouble with IIf Nz in an unbound control. Here's what I've got:
=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin],[SUcuin])

The way I need it to work is:
If [SUcuin] has a value then the unbound control needs to return this value.
If [SUcuin] is null then the unbound control needs to return the value in
[SumPVTotalcuin].

I believe I have it properly written however it behaves:
If [SUcuin] has a value then the unbound control returns the value in
[SumPVTotalcuin].
If [SUcuin] is null then the unbound control returns null.

Does anyone see what I'm doing wrong? Your help is greatly appreciated.

Thanks!

You're making it harder than it is.

NZ takes two arguments. If the first argument is non-NULL it returns whatever
value it has; if it is null, it returns the second argument.

Set the unbound control's control source to

=NZ([SUcuin], [SumPVTotalcuin])

John W. Vinson [MVP]

 

[Marshall Barton]

I'm having trouble with IIf Nz in an unbound control. Here's what I've got:
=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin],[SUcuin])

The way I need it to work is:
If [SUcuin] has a value then the unbound control needs to return this value.
If [SUcuin] is null then the unbound control needs to return the value in
[SumPVTotalcuin].

I believe I have it properly written however it behaves:
If [SUcuin] has a value then the unbound control returns the value in
[SumPVTotalcuin].
If [SUcuin] is null then the unbound control returns null.

The way you are using Nz is inappropriate.

This should be sufficient:
=Nz(SUcuin, sfrmFGPKConfigsPVs.Form!SumPVTotalcuin)

You could get an IIf to do the same thing:
=IIf(IsNull(SUcuin,sfrmFGPKConfigsPVs.Form!SumPVTotalcuin,SUcuin)

 

[Jeanette Cunningham]

John,
try this

=IIf(IsNull([SUcuin])),[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin],[SUcuin])Jeanette Cunningham"JohnLute" <[email protected]> wrote in messagenews:[email protected]...> I'm having trouble with IIf Nz in an unbound control. Here's what I'vegot:>=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin],[SUcuin])>> The way I need it to work is:> If [SUcuin] has a value then the unbound control needs to return thisvalue.> If [SUcuin] is null then the unbound control needs to return the value in> [SumPVTotalcuin].>> I believe I have it properly written however it behaves:> If [SUcuin] has a value then the unbound control returns the value in> [SumPVTotalcuin].> If [SUcuin] is null then the unbound control returns null.>> Does anyone see what I'm doing wrong? Your help is greatly appreciated.>> Thanks!>> --> www.Marzetti.com

 

[JohnLute]

Hi, Dave! Or should I say "SmartyPants"?

Ok. I gave it a whirl and Access automatically adds the [] accordingly:
=Nz([SUcuin],[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin])

If [SUcuin] has a value then it returns it however if [SUcuin] is null it
returns the dreaded #Error.

Is this because [SumPVTotalcuin] is an unbound control with a calculation as
its Control Source? I thought it might however I have another unbound control
that references it
=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin]/[SUcuin],"1.00")

That Nz returns fine which leads me to believe otherwise.

ARRRGGGHHH!

--
www.Marzetti.com

Simplify:
=Nz([SUcuin],sfrmFGPKConfigsPVs.Form!SumPVTotalcuin)
--
Dave Hargis, Microsoft Access MVP

I'm having trouble with IIf Nz in an unbound control. Here's what I've got:
=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin],[SUcuin])

The way I need it to work is:
If [SUcuin] has a value then the unbound control needs to return this value.
If [SUcuin] is null then the unbound control needs to return the value in
[SumPVTotalcuin].

I believe I have it properly written however it behaves:
If [SUcuin] has a value then the unbound control returns the value in
[SumPVTotalcuin].
If [SUcuin] is null then the unbound control returns null.

Does anyone see what I'm doing wrong? Your help is greatly appreciated.

Thanks!

 

[JohnLute]

WOW! More than one way to skin a cat! I've never understood who'd want to
skin a cat but...

I've tried everyone's suggestions here but none of them work. I hade to add
to yours:
=Nz([SUcuin],[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin])

This returns fine when there's a value in [SUcuin] however it returns #Error
when [SUcuin] is null and the value is in [SumPVtotalcuin].

Any ideas?

--
www.Marzetti.com

I'm having trouble with IIf Nz in an unbound control. Here's what I've got:
=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin],[SUcuin])

The way I need it to work is:
If [SUcuin] has a value then the unbound control needs to return this value.
If [SUcuin] is null then the unbound control needs to return the value in
[SumPVTotalcuin].

I believe I have it properly written however it behaves:
If [SUcuin] has a value then the unbound control returns the value in
[SumPVTotalcuin].
If [SUcuin] is null then the unbound control returns null.

Does anyone see what I'm doing wrong? Your help is greatly appreciated.

Thanks!

You're making it harder than it is.

NZ takes two arguments. If the first argument is non-NULL it returns whatever
value it has; if it is null, it returns the second argument.

Set the unbound control's control source to

=NZ([SUcuin], [SumPVTotalcuin])

John W. Vinson [MVP]

 

[Jeanette Cunningham]

John,
that is probably because you need to change the way you get the value of
SumPVTotalcuin from the subform
Instead of [sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin]
use Me.[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin])

So try
=Nz([SUcuin],Me.[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin])

Jeanette Cunningham

WOW! More than one way to skin a cat! I've never understood who'd want to
skin a cat but...

I've tried everyone's suggestions here but none of them work. I hade to
add
to yours:
=Nz([SUcuin],[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin])

This returns fine when there's a value in [SUcuin] however it returns
#Error
when [SUcuin] is null and the value is in [SumPVtotalcuin].

Any ideas?

--
www.Marzetti.com

I'm having trouble with IIf Nz in an unbound control. Here's what I've
got:
=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin],[SUcuin])

The way I need it to work is:
If [SUcuin] has a value then the unbound control needs to return this
value.
If [SUcuin] is null then the unbound control needs to return the value
in
[SumPVTotalcuin].

I believe I have it properly written however it behaves:
If [SUcuin] has a value then the unbound control returns the value in
[SumPVTotalcuin].
If [SUcuin] is null then the unbound control returns null.

Does anyone see what I'm doing wrong? Your help is greatly appreciated.

Thanks!

You're making it harder than it is.

NZ takes two arguments. If the first argument is non-NULL it returns
whatever
value it has; if it is null, it returns the second argument.

Set the unbound control's control source to

=NZ([SUcuin], [SumPVTotalcuin])

John W. Vinson [MVP]

 

[Jeanette Cunningham]

John,
to get the value from a textbox on a subform you have to call it with the
correct syntax.
Me.[CtlsubformName].Form.[CtlName]
where CtlsubformName is the name of the subform control. Your subform is
inside the subform control much the same way as a picture is inside an image
control.

Jeanette Cunnningham

WOW! More than one way to skin a cat! I've never understood who'd want to
skin a cat but...

I've tried everyone's suggestions here but none of them work. I hade to
add
to yours:
=Nz([SUcuin],[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin])

This returns fine when there's a value in [SUcuin] however it returns
#Error
when [SUcuin] is null and the value is in [SumPVtotalcuin].

Any ideas?

--
www.Marzetti.com

I'm having trouble with IIf Nz in an unbound control. Here's what I've
got:
=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin],[SUcuin])

The way I need it to work is:
If [SUcuin] has a value then the unbound control needs to return this
value.
If [SUcuin] is null then the unbound control needs to return the value
in
[SumPVTotalcuin].

I believe I have it properly written however it behaves:
If [SUcuin] has a value then the unbound control returns the value in
[SumPVTotalcuin].
If [SUcuin] is null then the unbound control returns null.

Does anyone see what I'm doing wrong? Your help is greatly appreciated.

Thanks!

You're making it harder than it is.

NZ takes two arguments. If the first argument is non-NULL it returns
whatever
value it has; if it is null, it returns the second argument.

Set the unbound control's control source to

=NZ([SUcuin], [SumPVTotalcuin])

John W. Vinson [MVP]

 

[JohnLute]

Thanks, Jeanette.

I gave it a try but it returns #Name?

I went a step further and threw in the form and other subform, too:
=Nz([SUcuin],[Me].[frmFGPackConfigurations].[sfrmFGPackConfigsPhysicalAtts].[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin])

That still returns #Name.

What is very confusing to me is that I have another unbound text box on the
same subform with the following AND it returns as expected:
=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin]/[SUcuin],"1.00")

So why doesn't this return:
=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin],[SUcuin])

???

--
www.Marzetti.com

John,
that is probably because you need to change the way you get the value of
SumPVTotalcuin from the subform
Instead of [sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin]
use Me.[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin])

So try
=Nz([SUcuin],Me.[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin])

Jeanette Cunningham

WOW! More than one way to skin a cat! I've never understood who'd want to
skin a cat but...

I've tried everyone's suggestions here but none of them work. I hade to
add
to yours:
=Nz([SUcuin],[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin])

This returns fine when there's a value in [SUcuin] however it returns
#Error
when [SUcuin] is null and the value is in [SumPVtotalcuin].

Any ideas?

--
www.Marzetti.com

On Fri, 11 Jan 2008 12:11:02 -0800, JohnLute

I'm having trouble with IIf Nz in an unbound control. Here's what I've
got:
=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin],[SUcuin])

The way I need it to work is:
If [SUcuin] has a value then the unbound control needs to return this
value.
If [SUcuin] is null then the unbound control needs to return the value
in
[SumPVTotalcuin].

I believe I have it properly written however it behaves:
If [SUcuin] has a value then the unbound control returns the value in
[SumPVTotalcuin].
If [SUcuin] is null then the unbound control returns null.

Does anyone see what I'm doing wrong? Your help is greatly appreciated.

Thanks!

You're making it harder than it is.

NZ takes two arguments. If the first argument is non-NULL it returns
whatever
value it has; if it is null, it returns the second argument.

Set the unbound control's control source to

=NZ([SUcuin], [SumPVTotalcuin])

John W. Vinson [MVP]

 

[Jeanette Cunningham]

John,
I thought the textbox was on the main form, but return to the previous
syntax if it is on the subform.
If the textbox is on the subform, I would expect that =NZ([SUcuin],
[SumPVTotalcuin])
would do what you want.
I don't understand why
=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin]/[SUcuin],"1.00")
would work if the unbound textbox and the textbox with SumPvTotalcuin are
both on the subform.

Jeanette Cunningham

Thanks, Jeanette.

I gave it a try but it returns #Name?

I went a step further and threw in the form and other subform, too:
=Nz([SUcuin],[Me].[frmFGPackConfigurations].[sfrmFGPackConfigsPhysicalAtts].[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin])

That still returns #Name.

What is very confusing to me is that I have another unbound text box on
the
same subform with the following AND it returns as expected:
=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin]/[SUcuin],"1.00")

So why doesn't this return:
=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin],[SUcuin])

???

--
www.Marzetti.com

John,
that is probably because you need to change the way you get the value of
SumPVTotalcuin from the subform
Instead of [sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin]
use Me.[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin])

So try
=Nz([SUcuin],Me.[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin])

Jeanette Cunningham

WOW! More than one way to skin a cat! I've never understood who'd want
to
skin a cat but...

I've tried everyone's suggestions here but none of them work. I hade to
add
to yours:
=Nz([SUcuin],[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin])

This returns fine when there's a value in [SUcuin] however it returns
#Error
when [SUcuin] is null and the value is in [SumPVtotalcuin].

Any ideas?

--
www.Marzetti.com


:

On Fri, 11 Jan 2008 12:11:02 -0800, JohnLute

I'm having trouble with IIf Nz in an unbound control. Here's what
I've
got:
=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin],[SUcuin])

The way I need it to work is:
If [SUcuin] has a value then the unbound control needs to return this
value.
If [SUcuin] is null then the unbound control needs to return the
value
in
[SumPVTotalcuin].

I believe I have it properly written however it behaves:
If [SUcuin] has a value then the unbound control returns the value in
[SumPVTotalcuin].
If [SUcuin] is null then the unbound control returns null.

Does anyone see what I'm doing wrong? Your help is greatly
appreciated.

Thanks!

You're making it harder than it is.

NZ takes two arguments. If the first argument is non-NULL it returns
whatever
value it has; if it is null, it returns the second argument.

Set the unbound control's control source to

=NZ([SUcuin], [SumPVTotalcuin])

John W. Vinson [MVP]

 

[JohnLute]

I don't understand why
=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin]/[SUcuin],"1.00")
would work if the unbound textbox and the textbox with SumPvTotalcuin are
both on the subform.

I'm very puzzled by all of this, too. Let me recap the design:

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")

As I noted this returns as expected.

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

This 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].

I hope that maps things out a little more clearly. Thanks for your continued
help.

 

[JohnLute]

I've stumbled on the my mistake.

=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin],[SUcuin])

This 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].

I reversed things in the code and now it works like this:
=IIf(Nz([SUcuin],0)<>0,[SUcuin],[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin])

UGH. I'm very sorry for the confusion. I don't think I explained myself
properly and on top of that was a bit confused with what I wanted to display
as I have two text boxes with similar functions.

Thanks to you and everyone for all the help!

--
www.Marzetti.com

I don't understand why
=IIf(Nz([SUcuin],0)<>0,[sfrmFGPKConfigsPVs].[Form]![SumPVTotalcuin]/[SUcuin],"1.00")
would work if the unbound textbox and the textbox with SumPvTotalcuin are
both on the subform.

I'm very puzzled by all of this, too. Let me recap the design:

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")

As I noted this returns as expected.

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

This 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].

I hope that maps things out a little more clearly. Thanks for your continued
help.