I have an Access 2007 Database with the following fields:

Counter (entries are 1 or 2) 1 is for a Single Person while 2 is for a Couple

Fullname: For a single this looks like: Henderson, Sarah
Fullname: For a Couple this looks like: Smithman, Harry & Diana

I want to be able to parse the fullname field to get Lastname: Henderson
Firstname: Sarah

and Lastname: Smithman
Firstname: Harry Note: No &
Spouse: Diana Note: No &

The ampersand (&) seems to be a problem for me in parsing the fullname field

Any help would be greatly appreciated

CodePudding user response：

You can use multiple Split for this:

Lastname = Split(Fullname, ", ")(0)
Firstname = Split(Split(Fullname, ", ")(1)," & ")(0)
Spouse = Split(Split(Fullname, ", ")(1) & " & "," & ")(1)