IIf and DLookup to Evaluate Nulls

[mgrabows]

I'm trying to evaluate nulls returned from a DLookup. If a Null comes in, all I want to do is change the text displayed in a textbox to "N/A". If it's not Null, I want to display a value from DLookup.

Below is the Control Source for the textbox:

IIf(DLookup("[equipExpiry]","tblEquipAttributes","equipID = [txtEquipID])" Is Null, "N/A", DLookUp("[equipExpiry]","tblEquipAttributes","equipID = " & [txtEquipID]))

Seems simple enough, but I'm getting #Name?.

Can anyone point out my problem? Thanks!

 

[pbaldy]

For starters you need an = preceding the formula. Secondly, use the Nz() function:

=Nz(DLookup(...), "N/A")

 

[mgrabows]

For starters you need an = preceding the formula. Secondly, use the Nz() function:

=Nz(DLookup(...), "N/A")

Thanks! Got it with the following code:

=Nz(DLookUp("[equipExpiry]","tblEquipAttributes","equipID = " & [txtEquipID]),"N/A")

Is there a reason why Me.txtEquipID as a criteria would return #NAME? ? Does it have to do with my syntax (see below) or something else?

=Nz(DLookUp("[equipExpiry]","tblEquipAttributes","equipID = " & Me.txtEquipID),"N/A")

 

[MarkK]

'Me' is a VBA language contruct that returns a reference to the object in which code is currently running. That is a completely different evaluation environment than the control source property of an Access control. VBA, for instance, doesn't return a #Name? error for unrecognized identifiers. An Access control can't execute sequential statements, and so ...