Hello,
Where you have an amount which may be NULL, you can use CASEWHEN to determine what value to actually use. For example:
CASEWHEN("Amount 1" IS NULL,0,"Amount 1") + CASEWHEN("Amount 2" IS NULL,0,"Amount 2 ") As "Sum"
This checks the field in question and if true uses the first value ( 0 in this case) or if false uses the second value (actual field amount here).
Edit:
Aside from the SQL answer in the comments, I finished the demo with a structure which may give you more flexibility. This is not meant as an answer for your current project, but possibly to give you some ideas on what I have been trying to say about structuring your DB better.
The file has 4 tables: Customers, Orders, Items and Filter (used for selection criteria). There are two forms and one Query. The first Form, CUSTOMERS
allows for entry of new customers (other information should be added to this record) and order information. Again more information needs to be added such as maybe an order date. The sample separates information based upon the customer and the order number.
Once you have this information go the the form OrderDisplay
. Here you can get information on an order for a customer. First select a customer from list & press Select Customer
button. This will fill the second listbox with that customers orders. Then select an order number from the second listbox and press the Select Order
button. This then fills out the rest of the form. The table grid displays individual items along with a calculated line total and below the grid are totals for the order.
There are no macros involved and no totals are stored in any of the records. All totals are calculated through either queries in the form or the single external query.
Hope this gives you a better idea of where you can progress to.
Sample: ShippingInvoicing.odb
One more thing. The data in the tables can then be used to print the same information using Report Builder. Report Builder has its’ own totaling capabilities.
If this answers your question please click on the (upper left area of answer).