vba - How to split Full Name field into First Name, Last Name and Middle Initial? -

i have table field called patrn name set first_name, last_name m.i.

examples:

smith, james m

jones, chris j.

anderson, wendy l

how can break field 3 different fields called first_name, last_name, , mi ? tried running query last_name: [patrn name]" & ", " last name didn't results. didn't design table way realize wasn't smart make full field name without individual field names; i'm in charge of fixing it.

consider whether can bend split function will.

here example immediate window session.

patrn_name = "smith, james m" ? patrn_name smith, james m ? split(patrn_name, ",")(0) smith ? trim(split(patrn_name, ",")(1)) james m ? split(trim(split(patrn_name, ",")(1)), " ")(0) james ? split(trim(split(patrn_name, ",")(1)), " ")(1) m 

you can't use split() in query directly. build 1 or more user-defined functions , call udf(s) query.

that approach make simpler query 1 requires combination of other functions: instr(), mid(), right(), etc. however, udf means query can work within access application session; if need query runs outside access (.net, vbscript, php, etc.), udf not available.

i suggest clarify whether intention extract first_name, last_name, , mi every time query data, or whether store values separately in table after extract them once. should influence approach choose. if split them out once , store, use vba procedure instead of query.

also decide should happen optional dot after middle initial, "jones, chris j.". keep in mi or discard it?