Dlookup - probably a basic question

ferijen

New Member
Joined
Nov 16, 2004
Messages
12
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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Last edited:
Upvote 0
Ok, it looks like the Criteria section regular quotes might be messed up as well.Try
Code:
=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...
 
Upvote 0
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...
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
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...
 
Upvote 0
=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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top