replace function with variables

[strewartha]

I am trying to use the replace function in an update query to eliminate text
in a field; the field contains first and last names. However, sometimes the
name contains an alternate name or unofficial name in parenthesis. For
example the field usually contains syntax like "John Doe" but sometimes
contains syntax like "William (Bill) Cherry".

I need a query that will get rid of all instances of the parenthises and the
name inside the parenthesis so the result is "William Cherry".

I have tried several things to no avail.

Any help would be appreciated.

 

[Jeff Boyce]

One way to approach this would be to isolate the various pieces, keeping
only those you want ... (actually, the optimal way to handle this would be
to NOT have the entire name in a single field, but to have separate pieces
of data {e.g., LastName, FirstName, ...} in separate fields!).

Take a look at Access HELP on the use and syntax for the Left(), Mid(),
Right() and InStr() functions. It may take several steps (and one way to do
this is to "chain" together multiple queries, each doing a single step, and
each next one relying on the previous for its input.

You'd be using those functions to do what a human being would have to do:

1. find any that don't have parentheses -- go to the first space, take
everything to the left of it as part and everything to the right of it as
the other part -- put the parts together.
2. find any that DO have parentheses -- take everything to the left of
the "open" parenthesis, take everything to the right of the "close"
parenthesis -- put the parts together.

Regards

Jeff Boyce
Microsoft Office/Access MVP