Unanswered: Splitting a number and looking up values
I have a table of employees in which each employee has a unique employee number. Each employee also has a SAP cost center which is not unique and has 10 digits. I want to find a way for access to take the first 3 digits of the 10-digit cost center and return a country name, take digits 4 and 5 to return a city and take digits 6 to 10 and return a department name. The employees cost center is input via a form (together with other info) and on the form I can use the Left(), Mid() and Right() functions to split the parts of the number into text boxes but can not figure out how to return the corresponding country, city or department names instead of numbers.