Results 1 to 2 of 2
  1. #1
    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!

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts