Parse name into 3 fields

[MaggieLeatherman]

Hi, I have a field called IWname in an old Access DB which contains the fullname ex.(Smith, Irene A.) I need to break up this field into EmpFirst,EmpMI,EmpLast..
Can someone quickly on what to do?

Many thanks, Maggie

 

[mp9]

You could use a combination of functions:

Left(string, n) can be used to substring the n left-most characters

Mid(string, m[, n]) can be used to substring from the m-th character for a length of n (or until the end)

Right(string, n) can be used to substring the n right-most characters

Instr(1,string1, string2, vbTextCompare) can be used to determine the value of n and m in the above functions.

So for example InStr(1, NameField, ",", vbTextCompare) would give you the position of the comma in your name field, so to get everything to the left of that you could use:

Left(NameField, InStr(1, NameField, ",", vbTextCompare) -1)

Note the "-1" becuase you need everything up to but not including the comma.

You can use such syntax in update queries to populate your new name fields.

Helpful?

 

[Crowley16]

or you could use the replace function to replace any separater character to a set delimiter value, then use split to split the thing out into an array...

however, do watch out for 2 or more delimiter values at once, that will give you an extra blank value in the array which will need to be handled...

--------------------
Procrastinate Now!

 

[MaggieLeatherman]

Thanks so much! Maggie