MS Access DLookup text field syntax issues

0
votes

Running into a DLookup Syntax Error in Access.

I am attempting to return the value located in the column [fld_linear_foot] from the table [tbl_model_number] -- my criteria column is titled [fld_model_number_official], and the criteria column must match a combobox [cbx_model_number]selection on my form.

=DLookUp([fld_linear_foot],[tbl_model_number],[fld_model_number_official]=[Forms]![frm_estimator]![cbx_model_number])

However, this gets a "#NAME?" error, which I think is because the data stored in the fld_model_number_official is text based, not numerical based.

I believe most of the error issue is stemming from [fld_model_number_official] is stored as text, not numerical. However, I have tried multiple layouts of quotation marks to attempt to get it treated as text, all which have not worked.

Can anybody shed some light on where this simple Dlookup is going wrong?

1
ms-access

1 Answers

0
votes

Enclose object names in quote marks. Embedding full path reference for combobox should work.

=DLookUp("[fld_linear_foot]", "[tbl_model_number]", "[fld_model_number_official]=[Forms]![frm_estimator]![cbx_model_number]")

Or concatenate variable references. If field is text type, use apostrophe delimiters.

=DLookUp("[fld_linear_foot]", "[tbl_model_number]", "[fld_model_number_official]='" & [cbx_model_number] & "'")

However, domain aggregate functions can slow performance. Better approach is to include fld_linear_foot in combobox RowSource and reference combobox column by index. If data is in 3rd column, its index is 2:

=[cbx_model_number].[Column](2)

Another approach is to include lookup table in form RecordSource and bind textboxes to fields. Set those controls as Locked Yes and TabStop No to prevent edit.