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.
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
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.
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