Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Mar 2001
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extracting Info from String (Access 2000)

    I'm using Access 2000 with Win 2000. I need to create a unique identifier from several fields in a table. Fields are Name and Customer Number. Example: Name=Smith,Mary (no spaces between comma and first name) and Customer Number=551077. I need the unique identifier to be SmiM551077 - first three letters of last name plus first initial plus customer number. If anyone can help or send me to a thread for this problem, I would be most appreciative.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extracting Info from String (Access 2000)

    You can do this in a query. In an emppty column, enter the following expression in the Field: row:

    UniqueID: Left([Name],3)&[Customer Number]

    The Left function extracts the first three characters of the name and & is the concatenation operator that combines strings.

    Notes:
    1. If someone has a last name of less than 3 characters, the comma from the name will be included in the new field.
    2. If you want a really unique identifier, use an AutoNumber field. It provides an ID number that is guaranteed to be unique within a table.

  3. #3
    Lounger
    Join Date
    Mar 2001
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting Info from String (Access 2000)

    My problem is getting the first initial of the first name. Both first and last names are in the same field and are separated by a comma. Example: Hollingsworth,Cecil. What I need to do is get HolC - that is the first three letters of the last name AND the first initial of the first name. I need the first letter past the comma and cannot figure out how to get it.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extracting Info from String (Access 2000)

    I'm sorry, I didn't read your original post closely enough. You can use the InStr function to get the position of the comma in the name field, and the Mid function to extract the first initial.

    InStr([Name],",") returns the position of the comma, for example if Name = "Jones,Jim", InStr(Name,",") = 6.
    InStr([Name],",")+1 returns the position of the first character past the comma, i.e. the first initial.
    Mid([Name],InStr([Name],",")+1,1) returns a string of length 1 from Name starting at the position past the comma, i.e. it returns the first initial.

    So the expression becomes:

    UniqueID: Left([Name],3)&Mid([Name],Instr([Name],",")+1,1)&[Customer Number]

  5. #5
    Lounger
    Join Date
    Jan 2003
    Location
    Herndon, Virginia, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting Info from String (Access 2000)

    <P ID="edit" class=small>(Edited by BrentD on 27-May-03 11:31. Looks like Hans beat me to the punch, but I'll leave this up here anyhow. [img]/forums/images/smilies/wink.gif[/img])</P>This should work for you: Left([Name],3) & Mid([Name],InStr([Name],",")+1,1) & [Customer Numer]

    What it's doing is finding the position of the comma using the InStr function, adding 1 to that number and then selecting the one character at that position using the Mid function.

Posting Permissions

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