Thread: Mathematical Query
02-15-12, 12:30 #1Registered User
- Join Date
- Jan 2012
Unanswered: Mathematical Query
Excuse the Newbie questions but I have a simple database of cars which I invoice charges against. So I am setting up an invoice query which adds up various fields across a number of tables. Most of these fields are simple numbers from other tables but I have created an OTHER CHARGES table to allow me to store random one off charges and add them to the invoice. For example one car could have 5 additional charges.
I use a main form to display the car details and have the additional charges on a subform. Using a field in the subform footer I've got a box on my main form which displays the total of all the sub form items against the car.
The first problem is that my invoice query does not work if any of the fields are blank. It doesn't treat them as zeros it just doesn't add up anything.
The second problem is that whilst I have the correct subform total on the main form I don't know how to get this into my invoice query.
Any help much appreciated!
02-15-12, 12:51 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
if they are blank then that almost certainly means they are 'null'. 'null' only has meaning in a database, wher eit means there is no value / the value is unknown.
to handle nulls use the NZ function which provides a value in place of null. usually its called silimar to
nz(mycolumnname, value if null)
nz(charge, 0)I'd rather be riding on the Tiger 800 or the Norton