Dlookup - probably a basic question

[ferijen]

Hi there

I'm much happier in Excel but have found myself building an Access database. I would like a Form (inputdata) to feed into the table (Maindataset).

Within the form (which will be for non specialist users) I would like a user to select a country from a drop down box. There is another table called 'country & region' and I would like the country selected from the drop down box to auto fill the field on the form (and table) with the associated region. So, user selects UK, region is Europe, user selects Hong Kong, region is China etc.

I'm almost there... I think... but its only returning 'China' as region no matter what country is selected. China is top of the 'country & region' table.

My code is

=DLookUp("[Country]","[Country & region]","'[Country]=[Country]'")

(where the first and last 'Country' comes from the 'Country & region' lookup table... I'm guessing that this ought to have a different name to the 'Country' which goes into the main table, but even when I've changed the names, its not helped)

(changing the code again if I changed the field names, it would be
=DLookUp("[Countrylookup]","[Country & region]","'[Country]=[Countrylookup]'") )

Can anyone help? I'm in access 2003 if it makes a difference.

 

[Montez659]

Two things that "look" incorrect (because I am not an expert ):
1. Try removing the extra single quotes surrounding the Criteria statement.
2. Try renaming the table removing the special characters and spaces. Something like CountryAndRegion, or Country_Region.

 

[Montez659]

Ok, it looks like the Criteria section regular quotes might be messed up as well.Try

=DLookUp("[Countrylookup]","[Country_Region]","[Country]= "[Countrylookup])

PS - I always have trouble with DLookup's as well, don't know why! If any of these suggestions works, let me know, because that might mean that I am on my way to understanding them better. Probably not...

 

[ferijen]

Thanks for the help, but I'm afraid I'm not getting anywhere, though I have renamed the fields. Also I need a reference into the region field, don't I.

So:

=DLookUp("[region_lookup]","[country_region_lookup]","'[Country]=[country_lookup)]'")

returns China...

 

[Montez659]

Did you try that last example where the quotes are moved? BTW, you didn't have to rename them all, just the one with the spaces and special characters.

And what do you mean by

Also I need a reference into the region field, don't I.

 

[Swift_74d]

Just for clarification;
What is the name of the field that contains the dropdown on the form?
What is the name of the field on the table that youd like your results to come from?
What is the name of the field on the table that you'd like to compare the dropdown choice to?
And just to be complete, what is the name of the table again?

 

[ferijen]

Hi Swift, here's my answers

Name of the field that contains the dropdown on the form: Country
Name of the field on the table that youd like your results to come from: region_lookup
Name of the field on the table that you'd like to compare the dropdown choice to: country_lookup
Name of the table again: country_region_lookup

Montez - the reference to the region field was that I was asking the lookup table to bring back the country, rather than the region, in my original expression.

Your example without the quotes gave me an error message, I'm afraid...

 

[Swift_74d]

=Dlookup("[region_lookup]","[country_region_lookup]","[country_lookup] ='" & [Country] & "'")

Should work, note the single quote just after the equals sign and another one at the end in between the two double quotes.

 

[Montez659]

Oh, I see...my bad. I had it in my head that it was coming from a form input. Sorry.

 

[ferijen]

Thank you Swift... but.. I've copied and pasted and it just comes up as a blank, sadly... I should be pasting this into ControlSource shouldn't I?