calculated field doesn't show approp. value

bbznyc

Registered User.
Local time
Today, 15:02
Joined
Aug 9, 2004
Messages
22
:( I have a problem on my hands. My DB is complete but now all sorts of problems begin to arise.

Situation 1: I have a box that calculates TotalBalance using this formula: =[InitialBalance]+[Commission]-Forms!Orders.Payment!Text8
to break this down, i have an initial balance, a commission and payments. InitialBalance and Commission is entered in the OrderForm. Payment is a continuous subform which runs this formula in the footer section: =Sum([Payments]). Therefore when payment received at a later date the user goes back to the order and enters the payment.

Problem 1: Everything works great when there is a payment, but when there isn't - i don't get a value for my TotalBalance. Same problem occurs in the Report that runs a similar quiery.

Situation 2: In the payment subform i have few fields PDate, Payment, and PaidBy.
Problem 2: How do i go about ensuring that the user will have not blank lines there or payments such as $0.


Thanks to all!
 
1. Use the Nz() function to handle rows with null values:
=Nz([InitialBalance],0) + Nz([Commission],0) - Nz(Forms!Orders.Payment!Text8, 0)

2. You can prevent rows with payments = 0 by setting a validation rule on the table --> <>0
I don't know what you mean by blank lines.
 
Thank you so much for replies. Situation #1 is completely solved. For Situation #2 I have attached the picture for a better explanation of blank lines. Thanks for your help again !!! YOU RULE! :)
subform.jpg

In this case a user can remove the values thus leaving the lines blank. I want the user to be able to do this freely but I don't want the lines to stay because when i look at the table i get blank entries there as well.
 
Last edited:
Since it doesn't make any sense to have transactions without a date, the TransactionDate should be required. Instruct the user to delete the record rather than "blanking" out the contents of fields and define the TransactionDate as required so a record without a date may not be saved.
 

Users who are viewing this thread

Back
Top Bottom