Auto Setting payment records to 0 for unpaid invoices

[Guest]

Hi! Have have an order entry system in which I have a customer form, an order
form and a payments form. Sometimes the customer sends a check and so there
is no payment untill the check arrives. therefore there is no payment record
for that invoice. When the invoice is printed out, I get and error under the
balance due because the Payment Amount field is null.
I have tried the bandad approach by using the Nz function on the invoice
report and in the balance due query. that works in some ways.

What I really want is the orders form is closed and no payments have been
made, I want to auto assign a $0.00 value to a record in payments so that the
invoice and other reports show correct numbers and not errors.
Any tips?

 

[Rick B]

I would not try to trick the system into this by adding a fake record.

The Nz function included in your formula is probably much closer to the
right solution (that is not a Band-Aid). You could also include an IIF
statement in your calculation.

A billing system should be able to process properly if no payments have been
made, without the need to add a fake record.

Without knowing what your formula and data structure are, we really can't
give you specific advise.

 

[Guest]

It's good to know I'm on the right track. The problem I'm having with the Nz
function is that is does not show on the invoice as a $0.00. It shows as 0.
And when a payment has been made it still shows on the invoice as a raw
number without the $ sign.
This is more of a presentation issue than a functional issue then.
but, I'm not sure how to get the presentation issue taken care of.
I have used the IIf(IsNull[Payment Amount],0) but it didn't work so I used
the simple Nz([Payment Amount],0). this statement gives a raw number on the
report.

 

[Rick B]

That's just a format. Just replace the "Expression" below with your
formula.

FormatCurrency(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit
[,UseParensForNegativeNumbers [,GroupDigits]]]])


or...

= "$"&Format(Expression, "##,##0.00") ' Returns "$1,111.11".
= Format(Expression, "###0.00") ' Returns "111.11".


--
Rick B

It's good to know I'm on the right track. The problem I'm having with the
Nz
function is that is does not show on the invoice as a $0.00. It shows as
0.
And when a payment has been made it still shows on the invoice as a raw
number without the $ sign.
This is more of a presentation issue than a functional issue then.
but, I'm not sure how to get the presentation issue taken care of.
I have used the IIf(IsNull[Payment Amount],0) but it didn't work so I used
the simple Nz([Payment Amount],0). this statement gives a raw number on
the
report.

I would not try to trick the system into this by adding a fake record.

The Nz function included in your formula is probably much closer to the
right solution (that is not a Band-Aid). You could also include an IIF
statement in your calculation.

A billing system should be able to process properly if no payments have
been
made, without the need to add a fake record.

Without knowing what your formula and data structure are, we really can't
give you specific advise.

 

[Guest]

You are the man! Thank you so much! It works!

That's just a format. Just replace the "Expression" below with your
formula.

FormatCurrency(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit
[,UseParensForNegativeNumbers [,GroupDigits]]]])


or...

= "$"&Format(Expression, "##,##0.00") ' Returns "$1,111.11".
= Format(Expression, "###0.00") ' Returns "111.11".


--
Rick B

It's good to know I'm on the right track. The problem I'm having with the
Nz
function is that is does not show on the invoice as a $0.00. It shows as
0.
And when a payment has been made it still shows on the invoice as a raw
number without the $ sign.
This is more of a presentation issue than a functional issue then.
but, I'm not sure how to get the presentation issue taken care of.
I have used the IIf(IsNull[Payment Amount],0) but it didn't work so I used
the simple Nz([Payment Amount],0). this statement gives a raw number on
the
report.

I would not try to trick the system into this by adding a fake record.

The Nz function included in your formula is probably much closer to the
right solution (that is not a Band-Aid). You could also include an IIF
statement in your calculation.

A billing system should be able to process properly if no payments have
been
made, without the need to add a fake record.

Without knowing what your formula and data structure are, we really can't
give you specific advise.


--
Rick B



Hi! Have have an order entry system in which I have a customer form, an
order
form and a payments form. Sometimes the customer sends a check and so
there
is no payment untill the check arrives. therefore there is no payment
record
for that invoice. When the invoice is printed out, I get and error
under
the
balance due because the Payment Amount field is null.
I have tried the bandad approach by using the Nz function on the
invoice
report and in the balance due query. that works in some ways.

What I really want is the orders form is closed and no payments have
been
made, I want to auto assign a $0.00 value to a record in payments so
that
the
invoice and other reports show correct numbers and not errors.
Any tips?