Have #Error ignore blank entries

[Guest]

I have a table in Acess where the last column uses the "Pmt" function to
calculate a mortgage monthly payment based on input from previous columns
that collect loan amount, interest rate, and terms. Everything works fine in
the "Pmt" function. I'm using this function to collect the montly payment
for up to 3 different loans.

Here's the issue. I may not always have 3 loans. I may only have one most
of the time. So if the user left LOAN AMOUNT blank, INTEREST blank, and
PAYMENT TERMS blank for the other 2 loans, how can I keep the "Pmt" function
from reading #Error?

What I'm trying to do is calculate the total of all monthly payments. So my
formula reads...

=[1st Month Payment]+[2nd Month Payment]+[3rd Month Payment]

..... where each Payment uses the "Pmt" function.

When I have calculated values for all 3, the formula works great. However,
if I only have 1 loan, it bombs and read #Error, because my other 2 loans
read #Error.

Here's a sample of what the form looks like right now to help simplify things:

Mort: Amount: Interest: Terms: Monthly Payment:
=========================================
#1 $167,750 4.5% 360 $846.79
#2
#Error
#3
#Error
=========================================
Total $167,750 #Error

#2, and #3 read #Error because I have no data in the previous columns. How
can I fix that? Even if I type zeroes, the formula still reads #Error. If I
fix this to be "null" if nothing is entered in the previous columns, it might
help.

In short, I need to either fix the "Pmt" function to read zero if nothing is
entered, or I need to somehow figure out a way for my TOTAL calculation to
ignore #Error values. Can either of these be done?

Thanks, and sorry for the long message. It was the only way to illustrate
my issue. Happy Holidays.

Frank

 

[tina]

you can use the Nz() function to convert a Null value to zero, as

Nz(FieldOrExpression, 0)

strategic use of the IIf() function may also be required, because some math
calculations will cause an error if one of the values you're manipulating is
a zero, as

IIf(Something = 0, do this to get around it, otherwise do the usual)

you can find both functions in Help, if you're not familiar with them.
usually i have to play with a "broken" expression, trying various
combinations of those functions, to get the expression to provide a valid
and correct value in all instances. (math was never my strong suit!)

hth

 

[Guest]

Tina,

Converting the null values to zero doesn't fix the problem. The formula
still reads #Error if the fields read zero.

Frank

you can use the Nz() function to convert a Null value to zero, as

Nz(FieldOrExpression, 0)

strategic use of the IIf() function may also be required, because some math
calculations will cause an error if one of the values you're manipulating is
a zero, as

IIf(Something = 0, do this to get around it, otherwise do the usual)

you can find both functions in Help, if you're not familiar with them.
usually i have to play with a "broken" expression, trying various
combinations of those functions, to get the expression to provide a valid
and correct value in all instances. (math was never my strong suit!)

hth

I have a table in Acess where the last column uses the "Pmt" function to
calculate a mortgage monthly payment based on input from previous columns
that collect loan amount, interest rate, and terms. Everything works fine in
the "Pmt" function. I'm using this function to collect the montly payment
for up to 3 different loans.

Here's the issue. I may not always have 3 loans. I may only have one most
of the time. So if the user left LOAN AMOUNT blank, INTEREST blank, and
PAYMENT TERMS blank for the other 2 loans, how can I keep the "Pmt" function
from reading #Error?

What I'm trying to do is calculate the total of all monthly payments. So my
formula reads...

=[1st Month Payment]+[2nd Month Payment]+[3rd Month Payment]

.... where each Payment uses the "Pmt" function.

When I have calculated values for all 3, the formula works great. However,
if I only have 1 loan, it bombs and read #Error, because my other 2 loans
read #Error.

Here's a sample of what the form looks like right now to help simplify things:

Mort: Amount: Interest: Terms: Monthly Payment:
=========================================
#1 $167,750 4.5% 360 $846.79
#2
#Error
#3
#Error
=========================================
Total $167,750 #Error

#2, and #3 read #Error because I have no data in the previous columns. How
can I fix that? Even if I type zeroes, the formula still reads #Error. If I
fix this to be "null" if nothing is entered in the previous columns, it might
help.

In short, I need to either fix the "Pmt" function to read zero if nothing is
entered, or I need to somehow figure out a way for my TOTAL calculation to
ignore #Error values. Can either of these be done?

Thanks, and sorry for the long message. It was the only way to illustrate
my issue. Happy Holidays.

Frank

 

[Jeff Boyce]

Frank

Pardon my intrusion...

Do I understand correctly? You are trying to store a calculated value in a
table? Access (JET) doesn't have "triggers", so how do you ensure that your
calculated payment amount gets recalculated any time there's a change in any
of the underlying columns used for the calculation?

And how do you resolve the underlying columns if someone makes a change to
your payment column amount?

If you scan the tablesdbdesign newsgroup, you'll see that the preferred
approach, generally, is to NOT store calculated values. Instead, create a
query that does the calculation. You can base a form on that query, so you
could see/add/edit the underlying loan amount fields, and still get a
displayed payment amount. You could also base a report on the query in the
same manner.

As for why your function isn't working, perhaps you could post the code in
your response...

--
Good luck

Jeff Boyce
<Access MVP>

Tina,

Converting the null values to zero doesn't fix the problem. The formula
still reads #Error if the fields read zero.

Frank

you can use the Nz() function to convert a Null value to zero, as

Nz(FieldOrExpression, 0)

strategic use of the IIf() function may also be required, because some math
calculations will cause an error if one of the values you're manipulating is
a zero, as

IIf(Something = 0, do this to get around it, otherwise do the usual)

you can find both functions in Help, if you're not familiar with them.
usually i have to play with a "broken" expression, trying various
combinations of those functions, to get the expression to provide a valid
and correct value in all instances. (math was never my strong suit!)

hth

I have a table in Acess where the last column uses the "Pmt" function to
calculate a mortgage monthly payment based on input from previous columns
that collect loan amount, interest rate, and terms. Everything works

fine
in

the "Pmt" function. I'm using this function to collect the montly payment
for up to 3 different loans.

Here's the issue. I may not always have 3 loans. I may only have one most
of the time. So if the user left LOAN AMOUNT blank, INTEREST blank, and
PAYMENT TERMS blank for the other 2 loans, how can I keep the "Pmt" function
from reading #Error?

What I'm trying to do is calculate the total of all monthly payments.

So
my

formula reads...

=[1st Month Payment]+[2nd Month Payment]+[3rd Month Payment]

.... where each Payment uses the "Pmt" function.

When I have calculated values for all 3, the formula works great. However,
if I only have 1 loan, it bombs and read #Error, because my other 2 loans
read #Error.

Here's a sample of what the form looks like right now to help simplify things:

Mort: Amount: Interest: Terms: Monthly Payment:
=========================================
#1 $167,750 4.5% 360 $846.79
#2
#Error
#3
#Error
=========================================
Total $167,750 #Error

#2, and #3 read #Error because I have no data in the previous columns. How
can I fix that? Even if I type zeroes, the formula still reads

#Error.
If I

fix this to be "null" if nothing is entered in the previous columns,

it
might

help.

In short, I need to either fix the "Pmt" function to read zero if

nothing
is

entered, or I need to somehow figure out a way for my TOTAL calculation to
ignore #Error values. Can either of these be done?

Thanks, and sorry for the long message. It was the only way to illustrate
my issue. Happy Holidays.

Frank

 

[tina]

yes, as i said, some math calculations will err when you're manipulating a
zero value - that's where the use of the IIf() function comes in. i'll refer
you to Jeff's post, both for the "saving a calculated value" advice, and to
move forward on fixing your expression.

Tina,

Converting the null values to zero doesn't fix the problem. The formula
still reads #Error if the fields read zero.

Frank

you can use the Nz() function to convert a Null value to zero, as

Nz(FieldOrExpression, 0)

strategic use of the IIf() function may also be required, because some math
calculations will cause an error if one of the values you're manipulating is
a zero, as

IIf(Something = 0, do this to get around it, otherwise do the usual)

you can find both functions in Help, if you're not familiar with them.
usually i have to play with a "broken" expression, trying various
combinations of those functions, to get the expression to provide a valid
and correct value in all instances. (math was never my strong suit!)

hth

I have a table in Acess where the last column uses the "Pmt" function to
calculate a mortgage monthly payment based on input from previous columns
that collect loan amount, interest rate, and terms. Everything works

fine
in

the "Pmt" function. I'm using this function to collect the montly payment
for up to 3 different loans.

Here's the issue. I may not always have 3 loans. I may only have one most
of the time. So if the user left LOAN AMOUNT blank, INTEREST blank, and
PAYMENT TERMS blank for the other 2 loans, how can I keep the "Pmt" function
from reading #Error?

What I'm trying to do is calculate the total of all monthly payments.

So
my

formula reads...

=[1st Month Payment]+[2nd Month Payment]+[3rd Month Payment]

.... where each Payment uses the "Pmt" function.

When I have calculated values for all 3, the formula works great. However,
if I only have 1 loan, it bombs and read #Error, because my other 2 loans
read #Error.

Here's a sample of what the form looks like right now to help simplify things:

Mort: Amount: Interest: Terms: Monthly Payment:
=========================================
#1 $167,750 4.5% 360 $846.79
#2
#Error
#3
#Error
=========================================
Total $167,750 #Error

#2, and #3 read #Error because I have no data in the previous columns. How
can I fix that? Even if I type zeroes, the formula still reads

#Error.
If I

fix this to be "null" if nothing is entered in the previous columns,

it
might

help.

In short, I need to either fix the "Pmt" function to read zero if

nothing
is

entered, or I need to somehow figure out a way for my TOTAL calculation to
ignore #Error values. Can either of these be done?

Thanks, and sorry for the long message. It was the only way to illustrate
my issue. Happy Holidays.

Frank