Avoiding "#Error" in Calculated Field

[Aaron5714]

In my database I have a form that shows details about a contract. This form has a calculated field that shows the last date that an audit was performed on the given contract. The control source for the calculated field is the following expression:

=DMax("[AuditDate]","tblContractAudit","[ContractID] = " & [Forms]![frmContractDetail]![ContractID])

This works fine, except when the user opens a blank version of the form to create a new record: until they start typing in any field, the autonumber ContractID field is not yet assigned a value which causes the calculated field to show #Error

I tried wrapping the whole expression in Nz() but that did not seem to have an effect. Any advice on how to prevent #Error from showing before the ContractID is generated?



Thanks,
Aaron

[Aaron5714]

I was able to solve the problem using an IIf function to first test whether the ContractID field is null. The updated expression is:

=IIf(IsNull([Forms]![frmContractDetail]![ContractID]),"",DMax("[AuditDate]","tblContractAudit","[ContractID] = " & [Forms]![frmContractDetail]![ContractID]))

If anyone knows of a more elegant way to do this please let me know.