Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2009
    Posts
    15

    Unanswered: help with expressions iff & IsNull if i have empty fields

    Hi there again
    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?

    i have a sub form that sums up all the expense amounts (total expenses) for a project and is displayed in the sub form.
    on my billing page i use an expression to grab the the total expenses figure from that sub 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.
    cheers

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6

  3. #3
    Join Date
    Sep 2009
    Posts
    15
    thanks for the reply but i don't think it helps me? is it more for macro and code, as i was looking for something to enter into the control source area as an expression, its currently =Sum([ExpenseAmount]).
    thanks for your time

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    =NZ(Sum([ExpenseAmount]),0) might work?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Sep 2009
    Posts
    15
    no luck, it still gives me an error in my totals

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    As noted in the link, the Nz() function won't work when no records are being returned. I typically use that function from the main form referring to the subform, but you can try it in the subform itself.
    Paul

  7. #7
    Join Date
    Sep 2009
    Posts
    15
    i understand what the link is telling me, i'm just not sure on how to enter it into my control source expression builder, every way i try i still get #error.

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Have you copied the function into a standard module? As a control source, it would be:

    =nnz(whatever)

    Whatever could be a reference to the subform textbox, or whatever.
    Paul

  9. #9
    Join Date
    Sep 2009
    Posts
    15
    i'm not sure what you mean when you say have you copied the function into a standard module, are you talking about copying it into visual basic in the code side of things?
    i have entered the following into my control source -
    =nnz([Project Expenses Subform].[Form]![Total Expenses],0)
    still returning #error

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Yes, it's a custom function so you have to copy it into a standard module in VBA.
    Paul

  11. #11
    Join Date
    Sep 2009
    Posts
    15
    got it working now, cheers for your help. fully understand.
    thank you all for your time.

Posting Permissions

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