=Nz([subform_credit].report!Total;0) broken

  • Thread starter Thread starter Didier Morandi
  • Start date Start date
D

Didier Morandi

Hi all,

I do not succeed to get a null value via the Nz function on a subform field when
that subform is *not* called due to lack of data to call it.

I explain.

In a form, I process invoices. If for a Customer, there are credit notes, I want
to subtract the total amount of credit notes to the total of invoices to give a
net debit to the Client.

If there are, I get my values ok and Bob is my uncle. If there are no, I get an
error in the following expression:

total_credit =Nz([subform_credit].report!Total;0)

I expected to get a null value when there are no credit notes, as in that case
the subform_credit routine is not called, but all I get is #ERROR# instead of "0".

What do you think?

Many thanks,

D.
Toulouse
 
Didier Morandi said:
Hi all,

I do not succeed to get a null value via the Nz function on a subform
field when that subform is *not* called due to lack of data to call
it.

I explain.

In a form, I process invoices. If for a Customer, there are credit
notes, I want to subtract the total amount of credit notes to the
total of invoices to give a net debit to the Client.

If there are, I get my values ok and Bob is my uncle. If there are
no, I get an error in the following expression:

total_credit =Nz([subform_credit].report!Total;0)

I expected to get a null value when there are no credit notes, as in
that case the subform_credit routine is not called, but all I get is
#ERROR# instead of "0".

What do you think?

Many thanks,

D.
Toulouse
Click to expand...

Keri Hardwick has posted an explanation and a workaround function here
on the Access Web:

http://www.mvps.org/access/forms/frm0022.htm
 
Dirk said:
Keri Hardwick has posted an explanation and a workaround function here
on the Access Web:

http://www.mvps.org/access/forms/frm0022.htm
Click to expand...

I love it. This does exactly what I need. Many thanks.

One more question.

My value is within a form field. How do I include this script into the form for
the parser to understand the nnz function? Directly in the VB source? What would
be the exact syntax then?

Tx.

D.
 
Didier Morandi said:
I love it. This does exactly what I need. Many thanks.

One more question.

My value is within a form field. How do I include this script into
the form for the parser to understand the nnz function? Directly in
the VB source? What would be the exact syntax then?

Tx.

D.
Click to expand...

I'm not entirely sure what you mean, but you would put the function into
standard module so it will be recognized from anywhere. Then you could
have a line of code like this:

total_credit =nnz([subform_credit]!Total)

or a controlsource for a text box like this:

=nnz([subform_credit]!Total)
 
what???
Dirk Goldgar said:
Didier Morandi said:
I love it. This does exactly what I need. Many thanks.

One more question.

My value is within a form field. How do I include this script into
the form for the parser to understand the nnz function? Directly in
the VB source? What would be the exact syntax then?

Tx.

D.
Click to expand...

I'm not entirely sure what you mean, but you would put the function into
standard module so it will be recognized from anywhere. Then you could
have a line of code like this:

total_credit =nnz([subform_credit]!Total)

or a controlsource for a text box like this:

=nnz([subform_credit]!Total)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top