Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002

    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.

    Anyone got some advice ?.

  2. #2
    Join Date
    Oct 2001
    Bay Area, CA
    use dlookup

    the example below uses a lookup table (tlkCountries) with CountryCode and CountryName and the name of the Country code text box on the form is called txtCountry

    dim strCountry as string

    strCountry = DLookup("[CountryName]", "tlkCountries", "[CountryCode] = " & me.txtCountry)

    hope this helps...

  3. #3
    Join Date
    Nov 2001
    Hi keeswijn,

    Miguel already answered the same question you posted several days ago.


  4. #4
    Join Date
    Apr 2002

    First, change your field, (in wich you record the 10 digits code), data type to text. If you want to split a number you can't, but text you can do.

    In the query, input the search criteria to:

    Like Forms![Form name].[First 3 Digits]

    Now the query only returns records where the first 3 digits matches the criteria.

    Use a dlookup function on that query.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts