NZ function & Nulls

  • Thread starter Thread starter Gaetanm via AccessMonster.com
  • Start date Start date
G

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
 
Gaetanm said:
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
Click to expand...


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

Marshall said:
I have a form with a subform. My subfor is [MYOB_Cards query subform]
Click to expand...
[quoted text clipped - 7 lines]
If I have data text20 no problem when its a null #error
Click to expand...

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)
Click to expand...
 
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

Marshall said:
I have a form with a subform. My subfor is [MYOB_Cards query subform]
Click to expand...
[quoted text clipped - 7 lines]
If I have data text20 no problem when its a null #error
Click to expand...

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)
Click to expand...
 
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

Gaetanm said:
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]
Click to expand...
Click to expand...
[quoted text clipped - 16 lines]
=IIf(IsError([MYOB_Cards query subform].Form.Text20), 0,
[MYOB_Cards query subform].Form!Text20)
Click to expand...
Click to expand...
 
Gaetanm said:
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
Click to expand...


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.
 
Back
Top