Using DLOOKUP fields in calculations

[simon123]

Hi there,

I have an issue with using what should hopefully be straightforward calculations. I want to work out the profit on something but I am struggling when a field with a DLOOKUP is introduced into the calculation.

E.g. in the pic below, I just want to work out the total cost of a session, but taking the session cost minus the rebate plus any additional costs. The additional costs field is based on a DLOOKUP which pulls a total from a query, and it works great if I have added extra costs but if there no extra costs, the calculation doesn't work. If the Additional Costs field (txtTotalCosts) is empty, I just want the calculation to treat it as a zero or ignore it. I know more Excel than Access, so I have tied things like =IIF(DLookUp("SumOfAddCostValue","qryTotalAddition alCostsPerEnquiry","[Enquiry ID]=" & [Enquiry ID])="",[SessionFee]-[Rebate],=[SessionFee]-[Rebate]+[txtTotalCosts]) but that doesn't work.

It's the same with the Gross Profit field. It can only calculate if there is data in the Total fees and total rate fields. I just need to treat them as zero if they are blank

Does anyone have any ideas?

Thanks very much

Simon

[moke123]

Have you tried nz()?
https://www.techonthenet.com/access/...dvanced/nz.php

[simon123]

Ahhh that's brilliant, thank you. Now I have learned a new function.

Works great, cheers!

[moke123]

Happy to help. Good luck with your project.