Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009

    help with expressions iff & IsNull if i have empty fields

    Hi there
    i am having trouble with a text box in a form, what i have is a billing form that shows total hours, hourly billing, expenses & total price.
    Total Hours - indicates total number of hours worked on a job
    Hourly Billing - Total hours x Billing rate
    Expenses - total Expenses (expenses table only has data if its entered)
    Total Price - Hourly Billing + Total Expenses

    this works fine if there are expenses, if there are none, i get #error.
    what statement do i need to enter in so that if there are no Expenses my total price will be just the hourly billing figure?
    thanks for your time

  2. #2
    Join Date
    Nov 2004
    out on a limb
    consider using the nz function to rturn a vlaue of zero if the column is null
    hourlybilling = nz(totalhours,0) * nz(Billingrate,0)
    totalexpenses = nz(hourlybiulling,0) + nz(ezpenses)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2009
    thanks for the reply
    how would i lay it out as my data comes from one form across to another.

    i have one form that sums up all the expense amounts (total expenses) for a project and displayed in this form.
    on my billing page i use an expression to grab the the total expenses figure from that form and displays it on my billing form. on the same billing form i then have a text box that sums the hourly billing figure and the total expenses figure and displays that value. my problem is that some projects may not incure any expenses therefore leaving the expenses form for this project blank and i get an #error displayed in my total expenses text area, this error then carries through to my billing form and for my total amount due i get #error.
    any suggestions.

Posting Permissions

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