NZ function & Nulls

[Gaetanm via AccessMonster.com]

I have a form with a subform. My subfor is [MYOB_Cards query subform]

In My main form I have a text field [text185] that I want to pull the data
from

[MYOB_Cards query subform]!Text20 this data are numbers

If there is no data in [Text20] I get #error I Tried in [text185]
=NZ([MYOB_Cards query subform]!Text20,0)

If I have data text20 no problem when its a null #error

Does anyone have an idea

Gaetanm

 

[Marshall Barton]

I have a form with a subform. My subfor is [MYOB_Cards query subform]

In My main form I have a text field [text185] that I want to pull the data
from

[MYOB_Cards query subform]!Text20 this data are numbers

If there is no data in [Text20] I get #error I Tried in [text185]
=NZ([MYOB_Cards query subform]!Text20,0)

If I have data text20 no problem when its a null #error

If the subform has no records, then there is no value to
"pull", not even Null.

You could check for the situation by using:

=IIf([MYOB_Cards query
subform].Form.RecordsetClone.RecordCount > 0, [MYOB_Cards
query subform].Form!Text20, 0)

or you can ignore anything that can generate #Error by
using:

=IIf(IsError([MYOB_Cards query subform].Form.Text20), 0,
[MYOB_Cards query subform].Form!Text20)

 

[Gaetanm via AccessMonster.com]

Marshall thanks for your quick responce

The value of pulling the data is that this field will be added to another to
get a total.
It doe not total too well will #error. I will try your sugestion and let you
know on my
next post

Gaetanm

I have a form with a subform. My subfor is [MYOB_Cards query subform]

[quoted text clipped - 7 lines]

If I have data text20 no problem when its a null #error

If the subform has no records, then there is no value to
"pull", not even Null.

You could check for the situation by using:

=IIf([MYOB_Cards query
subform].Form.RecordsetClone.RecordCount > 0, [MYOB_Cards
query subform].Form!Text20, 0)

or you can ignore anything that can generate #Error by
using:

=IIf(IsError([MYOB_Cards query subform].Form.Text20), 0,
[MYOB_Cards query subform].Form!Text20)

 

[Gaetanm via AccessMonster.com]

Marshall

I tried

=IIf([MYOB_Cards query
subform].Form.RecordsetClone.RecordCount > 0, [MYOB_Cards
query subform].Form!Text20, 0)
But now it comes back with #Name
Any Ideas?

Gaetanm

I have a form with a subform. My subfor is [MYOB_Cards query subform]

[quoted text clipped - 7 lines]

If I have data text20 no problem when its a null #error

If the subform has no records, then there is no value to
"pull", not even Null.

You could check for the situation by using:

=IIf([MYOB_Cards query
subform].Form.RecordsetClone.RecordCount > 0, [MYOB_Cards
query subform].Form!Text20, 0)

or you can ignore anything that can generate #Error by
using:

=IIf(IsError([MYOB_Cards query subform].Form.Text20), 0,
[MYOB_Cards query subform].Form!Text20)

 

[Gaetanm via AccessMonster.com]

Marshall

I used the following and that worked
=IIf(IsError([MYOB_Cards query subform].Form.Text20), 0,
[MYOB_Cards query subform].Form!Text20)

Thanks for the info if you get a chance I still would like to know
what the issue is with the other statement
Thanks

Marshall

I tried

=IIf([MYOB_Cards query
subform].Form.RecordsetClone.RecordCount > 0, [MYOB_Cards
query subform].Form!Text20, 0)
But now it comes back with #Name
Any Ideas?

Gaetanm

I have a form with a subform. My subfor is [MYOB_Cards query subform]

[quoted text clipped - 16 lines]

=IIf(IsError([MYOB_Cards query subform].Form.Text20), 0,
[MYOB_Cards query subform].Form!Text20)

 

[Marshall Barton]

I tried

=IIf([MYOB_Cards query
subform].Form.RecordsetClone.RecordCount > 0, [MYOB_Cards
query subform].Form!Text20, 0)
But now it comes back with #Name

Nothing obviously wrong there.

#Name means that Access could not resolve a name in the
expression, but the only thig I can think of in this
expression is maybe there's an extra space in the subform
control name.