Build an expression

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to build an expression that will show as a result a customers
balance as he makes dated payments. I have tried variations of the following
with no luck.
=[Sale Amount]-[Payment 1]-[Payment 2]-[Payment 3]-[Payment 4]

All of the above are fields within my table and query. THANKS in advance
for your help!!
 
Define "no luck". Does it work for those cases where all 5 fields have
values, but not for those cases where one (or more) of the fields doesn't
have a value?

If so, try =Nz([Sale Amount],0)-Nz([Payment 1],0)-Nz([Payment
2],0)-Nz([Payment 3],0)-Nz([Payment 4],0)

BTW, having field names like Payment1, Payment2, etc. is generally a sign
that your database hasn't been properly normalized. You'll probably find it
much easier to work with the data if you store each payment as a separate
row in a table, rather than as separate fields in a single row.
 
Back
Top