Replace "0" with a blank space

[Alvin]

I use access 2000
Using the following expression, If all feilds are null it currently gives a
value of "0".
How can I fix it so that the final result will be blank if all are null?

"Code is as follows"
=Val(Nz([STA1],0))+Val(Nz([STB1],0))+........

 

[Tom van Stiphout]

On Fri, 4 Jan 2008 17:24:00 -0800, Alvin

Check out the Nz function in the help file, and you'll probably end up
with something like:
=Nz([STA1], ' ') + Nz([STB1], ' ') + ...

-Tom.

 

[Alvin]

If " STA1 and STB1 " have any sort of value it gives me "#error"

what I am trying to accomplish is to keep from having "0" show up on the
report that will be printed from this form. I will be getting the sum of
"STA1 through STN1"
and if all are null I want to show null, Else show the sum. A little more
info on this. The calculation is being done in a text box on the form and not
the report or from a query. Hope this helps.
Thank you much

On Fri, 4 Jan 2008 17:24:00 -0800, Alvin

Check out the Nz function in the help file, and you'll probably end up
with something like:
=Nz([STA1], ' ') + Nz([STB1], ' ') + ...

-Tom.

I use access 2000
Using the following expression, If all feilds are null it currently gives a
value of "0".
How can I fix it so that the final result will be blank if all are null?

"Code is as follows"
=Val(Nz([STA1],0))+Val(Nz([STB1],0))+........

 

[Douglas J. Steele]

=IIf((Val(Nz([STA1],0))+Val(Nz([STB1],0))+........) = 0, Null,
Val(Nz([STA1],0))+Val(Nz([STB1],0))+........)

 

[Alvin]

I think this is how you are talking about.
But anyhow I tried it several diff ways and the properteis window won't let
me click out of it. As if I got something wrong with the code. I shortened it
up to just [STA1] & [STB1] and will add the rest when I get these two working
My code is listed below.
=IIf((Val(Nz([STA1],0))+Val(Nz([STB1],0))= 0, Null,
Val(Nz([STA1],0))+Val(Nz([STB1],0)))

Thank you Douglas for your help

=IIf((Val(Nz([STA1],0))+Val(Nz([STB1],0))+........) = 0, Null,
Val(Nz([STA1],0))+Val(Nz([STB1],0))+........)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I use access 2000
Using the following expression, If all feilds are null it currently gives
a
value of "0".
How can I fix it so that the final result will be blank if all are null?

"Code is as follows"
=Val(Nz([STA1],0))+Val(Nz([STB1],0))+........

 

[Krzysztof Pozorek [MVP]]

But what, if some component = 0? Result also will be null then. This is
incorrectly I think (question to author?). Result = null should be only, if
all components are null. I think, Alvin should write something like this
instead:
IIF(IsNull(STA1)+IsNull(STB1)+..., Null,
Val(Nz([STA1],0))+Val(Nz([STB1],0))+...)

K.P.
www.access.vis.pl

=IIf((Val(Nz([STA1],0))+Val(Nz([STB1],0))+........) = 0, Null,
Val(Nz([STA1],0))+Val(Nz([STB1],0))+........)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I use access 2000
Using the following expression, If all feilds are null it currently gives
a
value of "0".
How can I fix it so that the final result will be blank if all are null?

"Code is as follows"
=Val(Nz([STA1],0))+Val(Nz([STB1],0))+........

 

[Douglas J. Steele]

Yes, you're correct. I misread the original question.

Thanks for the correction.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

But what, if some component = 0? Result also will be null then. This is
incorrectly I think (question to author?). Result = null should be only,
if all components are null. I think, Alvin should write something like
this instead:
IIF(IsNull(STA1)+IsNull(STB1)+..., Null,
Val(Nz([STA1],0))+Val(Nz([STB1],0))+...)

K.P.
www.access.vis.pl

=IIf((Val(Nz([STA1],0))+Val(Nz([STB1],0))+........) = 0, Null,
Val(Nz([STA1],0))+Val(Nz([STB1],0))+........)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I use access 2000
Using the following expression, If all feilds are null it currently
gives a
value of "0".
How can I fix it so that the final result will be blank if all are null?

"Code is as follows"
=Val(Nz([STA1],0))+Val(Nz([STB1],0))+........

 

[Tom van Stiphout]

On Sat, 5 Jan 2008 03:32:02 -0800, Alvin

Ah, sorry, I misunderstood. I noticed you already have the correct
answer.
-Tom.

If " STA1 and STB1 " have any sort of value it gives me "#error"

what I am trying to accomplish is to keep from having "0" show up on the
report that will be printed from this form. I will be getting the sum of
"STA1 through STN1"
and if all are null I want to show null, Else show the sum. A little more
info on this. The calculation is being done in a text box on the form and not
the report or from a query. Hope this helps.
Thank you much

On Fri, 4 Jan 2008 17:24:00 -0800, Alvin

Check out the Nz function in the help file, and you'll probably end up
with something like:
=Nz([STA1], ' ') + Nz([STB1], ' ') + ...

-Tom.

I use access 2000
Using the following expression, If all feilds are null it currently gives a
value of "0".
How can I fix it so that the final result will be blank if all are null?

"Code is as follows"
=Val(Nz([STA1],0))+Val(Nz([STB1],0))+........