Trouble flipping name field in query!!!!

[govmate]

Hello I'm new to queries!

I'm having a problem writing an update query that will look at a name field ie (Dan Wilson Sr) and flip the name to a shortname ie (Wilson Sr Dan) with the criteria based to only flip the name if it contains Jr or Sr.
Below is an example of a select query I was using to test before updating my table.

SELECT *
FROM [Goodrec - All]
WHERE (instr(shortname,' JR')>0 Or instr(shortname,'SR')>0 Or instr(shortname,' III')>0) And instr(shortname,' MD')=0;

This query provides the following result for example, if name is "PAUL D TAYLOR SR" it is flipped to "SR PAUL D TAYLOR." I need shortname to display it like this "TAYLOR SR PAUL D"

Thanks in advance for any assistance!

[nico5038]

Hello I'm new to queries!

I'm having a problem writing an update query that will look at a name field ie (Dan Wilson Sr) and flip the name to a shortname ie (Wilson Sr Dan) with the criteria based to only flip the name if it contains Jr or Sr.
Below is an example of a select query I was using to test before updating my table.

SELECT *
FROM [Goodrec - All]
WHERE (instr(shortname,' JR')>0 Or instr(shortname,'SR')>0 Or instr(shortname,' III')>0) And instr(shortname,' MD')=0;

This query provides the following result for example, if name is "PAUL D TAYLOR SR" it is flipped to "SR PAUL D TAYLOR." I need shortname to display it like this "TAYLOR SR PAUL D"

Thanks in advance for any assistance!

The select query certainly won't produce the needed result.
It's even questionable or you'll ever get this to work flawlessly as there are too many possibilities to write a name (including typo's) and I stopped trying to create 100% automated changes to names that were entered fully in one field.
I switched to doing 80% of the obvious changes by code and leave the remainder to people to do it manually.
Normally I would only process the names having two words and save them in separate fields (Firstname and LastName). For Prefix and Suffix and MiddleInitial I would create fields and have them filled from a form where people can select part of the text in the original name and the selected text will be transported to the correct target fields. Having these fields I would re-construct the name in the way needed for reporting.

Getting the idea ?

Nic;o)

[missinglinq]

I actually enjoy working on this kind of problem, but as nic;o) indicated, trying to parse things like names with Jr, Sr, III and so forth on the end generally turns into a mare's nest! So before we get any deeper into this, I'd like to check a statement you made.

flip the name to a shortname ie (Wilson Sr Dan) with the criteria... only flip the name if it contains Jr or Sr.

Are you saying that you only want to do this if your original field ends in Jr or Sr, or do you want to flip all names? If all names, what titles beside Jr and Sr do you want to take into account? It's easier to write code correctly the first time than to go back and modify it later.

Welcome to TheScripts!

Linq ;0)>