Checking if data exists another table and, if yes, extracting a value

Apr 28, 2015
Reaction score
Suppose we have two tables:

- Table "Users" that contains columns "name" and "phone" (full phone number as integer)
- Table "Phonecodes" that contains columns "countryname" and "countrycode" (integer of 1-4 digits)

What I am looking to get is a table that contains columns "name", "phone" and "countryname".

I am having problems linking phone numbers with phone codes (phonecode is contained within first 1-4 digits of a phonenumber). My idea is to send a query into "Users" that will return columns "name" and "phone" and then add four extra columns...

DLookUp([countrycode], [Phonecodes], Left([phone],1)=[countrycode])
DLookUp([countrycode], [Phonecodes], Left([phone],2)=[countrycode])
DLookUp([countrycode], [Phonecodes], Left([phone],3)=[countrycode])
DLookUp([countrycode], [Phonecodes], Left([phone],4)=[countrycode])

...and then pick whichever of these four columns returns a non-null value via the IIF command. But, somehow, all four of the extra columns above return an #error.

Any input is welcome.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question