#Error! - trying to return a 0 value

[Justin]

I need to return a 0 value for the following expresssion:

=([Amount Spent - Payment subform].Form![Sum Of Amount]+[Amount Spent - Not
Payment subform1].Form![Sum Of Amount])

Basically, if the answer is 0, right now it reads #Error! and I would like
it to read 0.

Thanks!

 

[Jerry Whittle]

0 or null? If nulls are causing problems, you need to use the NZ function to
make the null something like a 0. Null + 2 = Null, not 2. Same goes for Null
+ 0.

 

[Justin]

Thanks, Jerry. I tried adding the nz function, but feel I've done something
wrong....thoughts?

0 or null? If nulls are causing problems, you need to use the NZ function to
make the null something like a 0. Null + 2 = Null, not 2. Same goes for Null
+ 0.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I need to return a 0 value for the following expresssion:

=([Amount Spent - Payment subform].Form![Sum Of Amount]+[Amount Spent - Not
Payment subform1].Form![Sum Of Amount])

Basically, if the answer is 0, right now it reads #Error! and I would like
it to read 0.

Thanks!

 

[BruceM]

Post what you tried. Without that information there really is no way even
to guess what went wrong.

Thanks, Jerry. I tried adding the nz function, but feel I've done
something
wrong....thoughts?

0 or null? If nulls are causing problems, you need to use the NZ function
to
make the null something like a 0. Null + 2 = Null, not 2. Same goes for
Null
+ 0.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I need to return a 0 value for the following expresssion:

=([Amount Spent - Payment subform].Form![Sum Of Amount]+[Amount Spent -
Not
Payment subform1].Form![Sum Of Amount])

Basically, if the answer is 0, right now it reads #Error! and I would
like
it to read 0.

Thanks!

 

[Justin]

I tried:

=Iif([Amount Spent - Payment subform].Form![Sum Of Amount]+[Amount Spent
-Not Payment subform1].Form![Sum Of Amount],0,0, [Amount Spent - Payment
subform].Form![Sum Of Amount]+[Amount Spent - Not Payment subform1].Form![Sum
Of Amount])

Thanks!
jw

Post what you tried. Without that information there really is no way even
to guess what went wrong.

Thanks, Jerry. I tried adding the nz function, but feel I've done
something
wrong....thoughts?

0 or null? If nulls are causing problems, you need to use the NZ function
to
make the null something like a 0. Null + 2 = Null, not 2. Same goes for
Null
+ 0.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I need to return a 0 value for the following expresssion:

=([Amount Spent - Payment subform].Form![Sum Of Amount]+[Amount Spent -
Not
Payment subform1].Form![Sum Of Amount])

Basically, if the answer is 0, right now it reads #Error! and I would
like
it to read 0.

Thanks!

 

[BruceM]

I see that you have added the IIf function, but there is no Nz function, and
the syntax for IIf is incorrect. Looking at the expression you originally
posted:
=([Amount Spent - Payment subform].Form![Sum Of Amount] + _
[Amount Spent - Not Payment subform1].Form![Sum Of Amount])

with the Nz function you would have this:
=Nz([Amount Spent - Payment subform].Form![Sum Of Amount],0) + _
Nz([Amount Spent - Not Payment subform1].Form![Sum Of Amount],0)

If SumOfAmount is null in either subform, 0 will be substituted for the
null.

I tried:

=Iif([Amount Spent - Payment subform].Form![Sum Of Amount]+[Amount Spent
-Not Payment subform1].Form![Sum Of Amount],0,0, [Amount Spent - Payment
subform].Form![Sum Of Amount]+[Amount Spent - Not Payment
subform1].Form![Sum
Of Amount])

Thanks!
jw

Post what you tried. Without that information there really is no way
even
to guess what went wrong.

Thanks, Jerry. I tried adding the nz function, but feel I've done
something
wrong....thoughts?

:

0 or null? If nulls are causing problems, you need to use the NZ
function
to
make the null something like a 0. Null + 2 = Null, not 2. Same goes
for
Null
+ 0.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I need to return a 0 value for the following expresssion:

=([Amount Spent - Payment subform].Form![Sum Of Amount]+[Amount
Spent -
Not
Payment subform1].Form![Sum Of Amount])

Basically, if the answer is 0, right now it reads #Error! and I
would
like
it to read 0.

Thanks!