Extracting Info from String (Access 2000)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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]

Reply | Quote

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

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.

Reply | Quote