Nested functions

  • Thread starter Thread starter Guest
  • Start date Start date
I am doing a lookup in a table to find a machine rate for a selected budget center on a form. Currently I am just doing a DLookup and it works great, except when some of the fields used in the calculation are blank. That's when I decided that I might want to put the DLookup function inside the IIf function. Well, Access won't let me. Every time I try to do that, it ignores what I typed and returns to the original calculation. Here is the DLookup function that work

=DLookUp("[MachRate]","[Budget Center]","[BudCenter] = Forms![833Master]![CurBudCenter]")*([CurMachSetupHrs]/[AOQ]

Thanks
Chris
 
Chris,

Use the Nz() function as shown below.
=DLookUp("[MachRate]","[Budget Center]","[BudCenter] =
Nz(Forms![833Master]![CurBudCenter], 0)*Nz([CurMachSetupHrs],0)/Nz([AOQ],1))

[AOQ] must default to 1 (if null) because division by zero will throw an
error.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Chris said:
I am doing a lookup in a table to find a machine rate for a selected
Click to expand...
budget center on a form. Currently I am just doing a DLookup and it works
great, except when some of the fields used in the calculation are blank.
That's when I decided that I might want to put the DLookup function inside
the IIf function. Well, Access won't let me. Every time I try to do that,
it ignores what I typed and returns to the original calculation. Here is
the DLookup function that works
=DLookUp("[MachRate]","[Budget Center]","[BudCenter] = Forms![833Master]![CurBudCenter]")*([CurMachSetupHrs]/[AOQ])

Thanks,
Chris
Click to expand...
 
Back
Top