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.
- 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.