NZ function

[Alain]

Hi to all,

I am using the NZ() function in some basic calulations ( [calculated field]
and [othercalcfield]) and it works great, I am getting an #ERROR when I am
trying to add the previous calculated together, I have tried multiple
combination of using the NZ()function but still getting the same thing


[calculated field] + [othercalcfield]
nz(([calculated field] + [othercalcfield]),0)
nz([calculated field],0) + nz([othercalcfield]),0)

Can anyone let me know what can cause this error?

Thanks

Alain

 

[Allen Browne]

Where are you trying this?

If in the Control Source of a text box:
- don't forget the equal sign at the start, and
- make sure the Name of this text box is not the same as the name of one of
the other fields.

If that does not solve the issue, it may be that Access is not understanding
the data types correctly. See:
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html

 

[Brendan Reynolds]

Your third example should work, provided both expressions evaluate to either
numeric values or Null, but you'll also need to handle the possibility that
they may be evaluated as text. Try ...

Val(NZ[ExpressionOne],0)) + Val(NZ([ExpressionTwo],0))

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Alain]

Thanks, the values that I use are currency only , no text, and still getting
the error msg

Alain

Your third example should work, provided both expressions evaluate to
either numeric values or Null, but you'll also need to handle the
possibility that they may be evaluated as text. Try ...

Val(NZ[ExpressionOne],0)) + Val(NZ([ExpressionTwo],0))

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Hi to all,

I am using the NZ() function in some basic calulations ( [calculated
field] and [othercalcfield]) and it works great, I am getting an #ERROR
when I am trying to add the previous calculated together, I have tried
multiple combination of using the NZ()function but still getting the same
thing


[calculated field] + [othercalcfield]
nz(([calculated field] + [othercalcfield]),0)
nz([calculated field],0) + nz([othercalcfield]),0)

Can anyone let me know what can cause this error?

Thanks

Alain

 

[Alain]

Hi Allen,

It is at the ControlSource of the text box that I write the calculation, and
yes the equal sign is there at the start, the data typoe that I use is
currency only, nothing else, I verified the names and they are not the
sames, each field is correctly identify
I will check the link

Alain

Where are you trying this?

If in the Control Source of a text box:
- don't forget the equal sign at the start, and
- make sure the Name of this text box is not the same as the name of one
of the other fields.

If that does not solve the issue, it may be that Access is not
understanding the data types correctly. See:
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi to all,

I am using the NZ() function in some basic calulations ( [calculated
field] and [othercalcfield]) and it works great, I am getting an #ERROR
when I am trying to add the previous calculated together, I have tried
multiple combination of using the NZ()function but still getting the same
thing


[calculated field] + [othercalcfield]
nz(([calculated field] + [othercalcfield]),0)
nz([calculated field],0) + nz([othercalcfield]),0)

Can anyone let me know what can cause this error?

 

[Brendan Reynolds]

Even though the underlying fields are currency, it is still possible that
the intermediate calculation may be evaluated as text. See the article at
the URL Allen posted. If you're still stuck after that, try posting the
intermediate calculation.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Thanks, the values that I use are currency only , no text, and still
getting the error msg

Alain

Your third example should work, provided both expressions evaluate to
either numeric values or Null, but you'll also need to handle the
possibility that they may be evaluated as text. Try ...

Val(NZ[ExpressionOne],0)) + Val(NZ([ExpressionTwo],0))

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Hi to all,

I am using the NZ() function in some basic calulations ( [calculated
field] and [othercalcfield]) and it works great, I am getting an #ERROR
when I am trying to add the previous calculated together, I have tried
multiple combination of using the NZ()function but still getting the
same thing


[calculated field] + [othercalcfield]
nz(([calculated field] + [othercalcfield]),0)
nz([calculated field],0) + nz([othercalcfield]),0)

Can anyone let me know what can cause this error?

Thanks

Alain

 

[Marshall Barton]

I am using the NZ() function in some basic calulations ( [calculated field]
and [othercalcfield]) and it works great, I am getting an #ERROR when I am
trying to add the previous calculated together, I have tried multiple
combination of using the NZ()function but still getting the same thing


[calculated field] + [othercalcfield]
nz(([calculated field] + [othercalcfield]),0)
nz([calculated field],0) + nz([othercalcfield]),0)

All three of those should work in one way or another. Check
the Format property of the two calculated text boxes to see
if they may be forcing the value to type Text.

 

[Alain]

Hi Brendan,

I renamed all my fields (using the same name) and now it is working just
fine, weird isn'it ???
Thanks for the help and info

Alain

Even though the underlying fields are currency, it is still possible that
the intermediate calculation may be evaluated as text. See the article at
the URL Allen posted. If you're still stuck after that, try posting the
intermediate calculation.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Thanks, the values that I use are currency only , no text, and still
getting the error msg

Alain

Your third example should work, provided both expressions evaluate to
either numeric values or Null, but you'll also need to handle the
possibility that they may be evaluated as text. Try ...

Val(NZ[ExpressionOne],0)) + Val(NZ([ExpressionTwo],0))

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted
without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Hi to all,

I am using the NZ() function in some basic calulations ( [calculated
field] and [othercalcfield]) and it works great, I am getting an
#ERROR when I am trying to add the previous calculated together, I
have tried multiple combination of using the NZ()function but still
getting the same thing


[calculated field] + [othercalcfield]
nz(([calculated field] + [othercalcfield]),0)
nz([calculated field],0) + nz([othercalcfield]),0)

Can anyone let me know what can cause this error?

Thanks

Alain

 

[Allen Browne]

Interesting!

If just changing the names solved the problem for you, I think there's a
good chance that this problem was caused by Name AutoCorrect causing Access
to get confused about what you were referring to.

For a partial list of these problems, see:
Failures caused by Name Auto-Correct
at:
http://members.iinet.net.au/~allenbrowne/bug-03.html

Glad you got is solved!

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Brendan,

I renamed all my fields (using the same name) and now it is working just
fine, weird isn'it ???
Thanks for the help and info

Alain

Even though the underlying fields are currency, it is still possible that
the intermediate calculation may be evaluated as text. See the article at
the URL Allen posted. If you're still stuck after that, try posting the
intermediate calculation.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Thanks, the values that I use are currency only , no text, and still
getting the error msg

Alain



"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
Your third example should work, provided both expressions evaluate to
either numeric values or Null, but you'll also need to handle the
possibility that they may be evaluated as text. Try ...

Val(NZ[ExpressionOne],0)) + Val(NZ([ExpressionTwo],0))

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with
a
GlobalSign digital certificate is a forgery and should be deleted
without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Hi to all,

I am using the NZ() function in some basic calulations ( [calculated
field] and [othercalcfield]) and it works great, I am getting an
#ERROR when I am trying to add the previous calculated together, I
have tried multiple combination of using the NZ()function but still
getting the same thing


[calculated field] + [othercalcfield]
nz(([calculated field] + [othercalcfield]),0)
nz([calculated field],0) + nz([othercalcfield]),0)

Can anyone let me know what can cause this error?

Thanks

Alain