Dlookup Help

[www]

Hello, I have a form (f_on_farm_entry) where I use a combo box to select a county. I am trying to get a text-box on the same form to display the county code for the county that is selected. The County Code is which is stored in another table ("County Code") in a column named CoNum


Here is the expression I am trying to use: =DLookUp("CoNum","County Code","County=" & Forms![f_on_farm_entry].cbo_county)

I keep getting an error an was hoping someone could see what I was doing wrong.



Thanks,

[ranman256]

You should not use names with spaces. If you do put them in brackets.
"[country codes]"

[www]

Thank you. I am still getting an error though. I changed County Code to County_Code. Here is what I am using now:

=DLookUp("CoNum","County_Code","County=" & [Forms]![f_on_farm_entry].[cbo_county])

here is my county code table, and I want "CoNum" displayed in the text box:

ID	County	CoNum
6	ALAMEDA	06001-

[ranman256]

You 1st had a space in the table,
now you have an underscore.
which is it?

if the table has a space, YOU MUST USE BRACKETS.

[JoeM]

Since County looks like a text field, I think you need Text Qualifiers around the value you are returning from your Form, i.e.

=DLookUp("CoNum","County_Code","County='" & [Forms]![f_on_farm_entry].[cbo_county] & "'")

[www]

Sorry, I was meaning to clarify that I changed the table name to remove the space to an underscore

[www]

Thank you Joe, I used what you provided, but now the box where I want the result is empty instead of stating "error". Sorry I am trying to learn this stuff and am obviously not a database person.

[JoeM]

If possible, it might be best to upload your database (or at least a scaled down version of it), for analysis.

[www]

Here is a very basic version of it, but the table structure for these two tables should be the same. I had to put in a zip file

[June7]

Weird thing about domain aggregate functions is they will accept table name with space without brackets. I also recommend not to use spaces nor punctuation/special characters (underscore only exception) in names. Nor reserved words as names.

However, DLookup is not necessary. Change the combobox properties:

RowSource: SELECT [ID], [County], [CoNum] FROM County_Code ORDER BY [County];
ColumnCount: 3
ColumnWidths: 0";1";0

Then expression in textbox: =[cbo_county].[Column](2)

[www]

Weird thing about domain aggregate functions is they will accept table name with space without brackets.

However, DLookup is not necessary. Change the combobox properties:

RowSource: SELECT [ID], [County], [CoNum] FROM County_Code ORDER BY [County];
ColumnCount: 3
ColumnWidths: 0";1";0

Then expression in textbox: =[cbo_county].[Column](2)

That worked perfect, Thanks!

I also built on that to concatenate with he APN on the form and got what I wanted a county code with he apn =[cbo_county].[column](2) & [APNs]

Thanks again,