DLookup and ELookup errors

[MadTom]

I my form I'm using Dlookup for a text box control source and I get an "#error" in the text box when I go to a new record.
If I use Elookup with the same code I get "syntax error (missing operator) query expression" when I go to add new record.
I know the problem is a Null in the data, any way to fix this problem? The easy way is to use Dlookup.
I have searched this topic.


Thanks,
Tom

=DLookUp("[StorageType]","[tblBins]","[Bin_ID] =" & [Forms]![frmParts]![cboLocation] & "")

[June7]

Instead of DLookup, use multi-column combobox that includes related fields in RowSource. Then expression in textbox references combobox column by index, index begins with 0.

=cboLocation.Column(1)

Otherwise handle Null with Nz(). Is Bin_ID a number type?

=DLookUp("[StorageType]","[tblBins]","[Bin_ID] =" & Nz([Forms]![frmParts]![cboLocation],0))

[MadTom]

Thanks! Your expression below works. StorageType is a String, Bin_ID is a Number

=DLookUp("[StorageType]","[tblBins]","[Bin_ID] =" & Nz([Forms]![frmParts]![cboLocation],0))

Tom

[June7]

I still prefer combobox index method. Domain aggregate functions can be noticeably slow on forms.

[MadTom]

Ok, I'm using

=cboLocation.Column(1) and it works fine, not sure how, will look into it.
Thanks,
Tom