UPDATE query to rearrangement name field

[gojets1721]

I have a name field which is formatted as "LastName, First Name".

I want to rearrange it to simply be "FirstName LastName" and get rid of the comma. Any suggestions on how to write that in an update query?

I feel like I found a guide online to do this before, but for the life of me, I can't find it.

 

[theDBguy]

The Split() function might work well for something like that; unfortunately, it doesn't work in queries. So, you might first create a function to switch the names that you can then use in your query.

 

[gojets1721]

The Split() function might work well for something like that; unfortunately, it doesn't work in queries. So, you might first create a function to switch the names that you can then use in your query.

Thanks. So I tried the below and it almost worked:

Right([yourfieldname],Len([yourfieldname])-InStr([yourfieldname],",")) & " " & Left([yourfieldname],InStr([yourfieldname],",")-1)

It swaps them perfectly except it leaves a space at the start of the field. I'm not sure how to fix that with the above code.

Anyone have any suggestions

 

[Josef P.]

it leaves a space at the start of the field.

Trim(...)

And if you had 2 data fields (FirstName and LastName), you could arrange them any way you want in a query.

 

[plog]

One datum per field. Don't cram multiple pieces of information into just one field.

Make a FirstName and a LastName field and extract your improper field's data into there.

 

[Pat Hartman]

Use Mid instead of Right. It simplifies the code. You also have to calculate the offset correctly. You don't want to start immediately following the comma, you need to skip 1 character.

Mid([yourfieldname], InStr([yourfieldname],",") +1) & " " & Left([yourfieldname],InStr([yourfieldname],",")-1)