Functions

message from =?Utf-8?B?Q3ludGhpYSBT?= on 5 May 2004

Does anyone know of an easy way to switch words in a field? For example, a field has John Smith - on 2000 records we need to put Smith first so it can be sorted.

I would be most grateful for any ideas.

I have tried several functions but they require the exact number of characters. I also tried StrReverse (which sounded good) and it did move Smith to the beginning, but it put htimS nhoJ - -not very helpful.

Thanks.

Lynn Trapp replied to =?Utf-8?B?Q3ludGhpYSBT?= on 5 May 2004

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

The first part (Mid()) strips out the last name and the second part (Left())
strips out the first name then concatenates them together with a comma
between.

=?Utf-8?B?Q3ludGhpYSBT?= replied to Lynn Trapp on 5 May 2004

Thank you !!

That works beautifully - I knew there had to be a way to do it.

Tom Wickerath replied to Lynn Trapp on 6 May 2004

For the more complicated cases, you can use Splitter for Microsoft Access to split the names into
separate fields. I have this tool and it works great.

http://www.infoplan.com.au/splitter/

Tom
_______________________________________

"Lynn Trapp" <ltrappNoSpam@ltcomputerdesigns.com> wrote in message
news:ewBGuZuMEHA.1468@TK2MSFTNGP12.phx.gbl...
Cynthia,
This is not perfect, but will get all those records where you have 2 names
in the field:

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

The first part (Mid()) strips out the last name and the second part (Left())
strips out the first name then concatenates them together with a comma
between.

Archived message: Functions (Microsoft Access Forms)