I'm trying to port some employee information over from an older database that doesn't have the information in exactly the same type of format as I need it in.
For example, I have first name, middle initial and last name fields. The older database has just a name field with the name in the "lastname, firstname MI" format. Is there any way that, through code and/or append queries, that I could convert their whole name field to the individual fields?
Obviously, there's a clear pattern in every name. You can grab everything before the comma for the last name. Everything after the comma-space to the second space for the first name and then whatever's left to the MI, if there is one. I know I could use code, left$ and similar things, to sort through a string, but I'm unsure how to do it within a query going through hundreds of records.
You can determine the position of the Comma and the space using the Instr, thenyou can use the Mid$ twice, one to extract the words till the comma , and one to extract the word form the comma +1 till the space