iff and dlookup to populate a text box

[basikstuff]

Hey everyone,

I am designing a database that handles event tracking for a catering company. They provide everything an event could need: food/beverage/staffing/rentals.

A problem I encountered is with populating text boxes to calculate costs. When an event has all items (food excluded) associated, costs are calculated just fine. However, when an event is missing one item (be it beverage, staffing, or rentals) subtotals, tax, and so forth do not get calculated; instead, they remain blank.

I am using the dlookup function to pull values from queries to populate the text boxes.

=DLookUp("[rentalTotal] - ([rentalTotal]*0.15)","rentalSumQRY","eventTBL.id = " & Forms!eventEntryFRM!id)

Associated Query:

SELECT Sum(rentalTBL.cost*rentalServiceTBL.qty) AS rentalTotal
FROM rentalTBL INNER JOIN (eventTBL INNER JOIN rentalServiceTBL ON eventTBL.id=rentalServiceTBL.eventID) ON rentalTBL.id=rentalServiceTBL.rentalID
WHERE (((eventTBL.id)=Forms!eventEntryFRM!id));

If an event doesn't have a beverage/staff/rental item associated with it, the query returns with a single blank cell.

I tried using the iif function but I am unsure what the dlookup evaluates to if there is no value (null or blank string?). Basically, what I was trying to accomplish with this was: if there are no values, set the text box to 0, otherwise, set it to what is returned by the dlookup funciton.

iif (( DLookUp("[rentalTotal]","rentalSumQRY","eventTBL.id = " & Forms!eventEntryFRM!id) = "" ), "0", (DLookUp("[rentalTotal]","rentalSumQRY","eventTBL.id = " & Forms!eventEntryFRM!id)))

If there needs to be any clarification, please let me know. Any advice/suggestion is greatly appreciative!

PS. While reviewing all this, I get the feeling that there is a bit of redundancy.

 

[boblarson]

To handle NULLS you need to encapsulate each individual field with Nz:

NZ([Field1],0) + Nz([Field2],0)...etc.

 

[basikstuff]

To handle NULLS you need to encapsulate each individual field with Nz:

NZ([Field1],0) + Nz([Field2],0)...etc.

This works! It was exactly was I was looking for. Thanks bob!