update query to remove parenthesis

Hi Folks,

I have a field [MaidenName] that contains parenthesis, which I'd like to
remove.

Currently the data in the field looks like:
(Miller)

There's only 120 names that need to have the parenthesis stripped. By
the time I figure out how to do this in a query I could have done it by
hand, but I am determined to learn how to do this!

I've managed to strip the left paren with the following, but can't
figure out how to get rid of the right one:
MaidFix:
Mid([SP_MaidenName],InStr([SP_MaidenName],'(')+1,Len([SP_MaidenName])-InStr([SP_MaidenName],'
'))

Is there an easier way? If not, how do I change the statement above to
remove the right paren?

Your help is very much appreciated!

Kathy R.

This sounds like a one time task. If so, you can simply view the field in
datasheet view and use Find And Replace from the edit menu.

--
Duane Hookom
Microsoft Access MVP


"Kathy R." wrote:

Hi Folks,

I have a field [MaidenName] that contains parenthesis, which I'd like to
remove.

Currently the data in the field looks like:
(Miller)

There's only 120 names that need to have the parenthesis stripped. By
the time I figure out how to do this in a query I could have done it by
hand, but I am determined to learn how to do this!

I've managed to strip the left paren with the following, but can't
figure out how to get rid of the right one:
MaidFix:
Mid([SP_MaidenName],InStr([SP_MaidenName],'(')+1,Len([SP_MaidenName])-InStr([SP_MaidenName],'
'))

Is there an easier way? If not, how do I change the statement above to
remove the right paren?

Your help is very much appreciated!

Kathy R.
.

On Tue, 23 Feb 2010 19:14:35 -0500, "Kathy R."
wrote:

Hi Folks,

I have a field [MaidenName] that contains parenthesis, which I'd like to
remove.

Currently the data in the field looks like:
(Miller)

There's only 120 names that need to have the parenthesis stripped. By
the time I figure out how to do this in a query I could have done it by
hand, but I am determined to learn how to do this!

I've managed to strip the left paren with the following, but can't
figure out how to get rid of the right one:
MaidFix:
Mid([SP_MaidenName],InStr([SP_MaidenName],'(')+1,Len([SP_MaidenName])-InStr([SP_MaidenName],'
'))

Is there an easier way? If not, how do I change the statement above to
remove the right paren?

Your help is very much appreciated!

Kathy R.

In A2002 and later, you can use the builtin Replace() function. Update
MaidenName to

Replace(Replace([MaidenName], "(", ""), ")", "")

This will replace both parens with an empty string, converting "(Miller)" to
"Miller".
--

John W. Vinson [MVP]

Thank you both! I had a feeling I was overthinking it. I've recently
migrated from 2000 to 2007 and didn't know there was a "find and
replace." And even if I did, looking at , "(", ""), ")", "") tends to
make me crosseyed. It reminds me of morse code.

Could you help me break it down so I can understand it better? Thank
you in advance for taking the time to teach!

The syntax is Replace(expression, find, replace [, start ] [, count ] [,
compare ] )

Replace(Replace([MaidenName], "(", ""), ")", "")

expression - ([MaidenName], why is there an open paren here and it's
matching closed paren after the replace?
find - "(" find the left paren
replace ""), replace it with nothing/blank
start - ")" I don't understand this. It seems to say "remove the
closing paren" but it's supposed to "start" somewhere
count - "") make all possible substitutions
compare - optional, not used

Can you substitute a letter (let's say the data is Miller, Mason,
Maxim...) and I want to remove all of the "M"s at the beginning of each
name.

Kathy R.

John W. Vinson wrote:
On Tue, 23 Feb 2010 19:14:35 -0500, "Kathy R."
wrote:

Hi Folks,

I have a field [MaidenName] that contains parenthesis, which I'd like to
remove.

Currently the data in the field looks like:
(Miller)

There's only 120 names that need to have the parenthesis stripped. By
the time I figure out how to do this in a query I could have done it by
hand, but I am determined to learn how to do this!

I've managed to strip the left paren with the following, but can't
figure out how to get rid of the right one:
MaidFix:
Mid([SP_MaidenName],InStr([SP_MaidenName],'(')+1,Len([SP_MaidenName])-InStr([SP_MaidenName],'
'))

Is there an easier way? If not, how do I change the statement above to
remove the right paren?

Your help is very much appreciated!

Kathy R.

In A2002 and later, you can use the builtin Replace() function. Update
MaidenName to

Replace(Replace([MaidenName], "(", ""), ")", "")

This will replace both parens with an empty string, converting "(Miller)" to
"Miller".

It's the result of a call Replace nested within another call to Replace. It
might help to break it down like this:

Replace
(
Replace
(
[MaidenName],
"(",
""
),
")",
""
)

so that the opening and closing parentheses are in the same columns.

In the syntax you quoted from online help, the parts enclosed in brackets
are optional. They are not used in John's example. expression can be a
literal string, a variable, or a call to a function that returns a string.
The latter is what John took advantage of. This part:
Replace
(
[MaidenName],
"(",
""
)
returns a string that has all opening parens replaced by empty strings. This
is fed as the expression to the outer Replace which replaces all the closing
parens. Clear?



Kathy R. wrote:
Thank you both! I had a feeling I was overthinking it. I've recently
migrated from 2000 to 2007 and didn't know there was a "find and
replace." And even if I did, looking at , "(", ""), ")", "") tends to
make me crosseyed. It reminds me of morse code.

Could you help me break it down so I can understand it better? Thank
you in advance for taking the time to teach!

The syntax is Replace(expression, find, replace [, start ] [, count ]
[, compare ] )

Replace(Replace([MaidenName], "(", ""), ")", "")

expression - ([MaidenName], why is there an open paren here and it's
matching closed paren after the replace?
find - "(" find the left paren
replace ""), replace it with nothing/blank
start - ")" I don't understand this. It seems to say "remove the
closing paren" but it's supposed to "start" somewhere
count - "") make all possible substitutions
compare - optional, not used

Can you substitute a letter (let's say the data is Miller, Mason,
Maxim...) and I want to remove all of the "M"s at the beginning of
each name.

Kathy R.

John W. Vinson wrote:
On Tue, 23 Feb 2010 19:14:35 -0500, "Kathy R."
wrote:

Hi Folks,

I have a field [MaidenName] that contains parenthesis, which I'd
like to remove.

Currently the data in the field looks like:
(Miller)

There's only 120 names that need to have the parenthesis stripped. By
the time I figure out how to do this in a query I could have done
it by hand, but I am determined to learn how to do this!

I've managed to strip the left paren with the following, but can't
figure out how to get rid of the right one:
MaidFix:
Mid([SP_MaidenName],InStr([SP_MaidenName],'(')+1,Len([SP_MaidenName])-InStr([SP_MaidenName],'
'))

Is there an easier way? If not, how do I change the statement
above to remove the right paren?

Your help is very much appreciated!

Kathy R.

In A2002 and later, you can use the builtin Replace() function.
Update MaidenName to

Replace(Replace([MaidenName], "(", ""), ")", "")

This will replace both parens with an empty string, converting
"(Miller)" to "Miller".

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Ah, nested! Now it makes more sense. Thank you for the explanation and
the breakdown. I am a "visual" learner and it really helped to have it
typed out like that!

Kathy R.

Bob Barrows wrote:
It's the result of a call Replace nested within another call to Replace. It
might help to break it down like this:

Replace
(
Replace
(
[MaidenName],
"(",
""
),
")",
""
)

so that the opening and closing parentheses are in the same columns.

In the syntax you quoted from online help, the parts enclosed in brackets
are optional. They are not used in John's example. expression can be a
literal string, a variable, or a call to a function that returns a string.
The latter is what John took advantage of. This part:
Replace
(
[MaidenName],
"(",
""
)
returns a string that has all opening parens replaced by empty strings. This
is fed as the expression to the outer Replace which replaces all the closing
parens. Clear?



Kathy R. wrote:
Thank you both! I had a feeling I was overthinking it. I've recently
migrated from 2000 to 2007 and didn't know there was a "find and
replace." And even if I did, looking at , "(", ""), ")", "") tends to
make me crosseyed. It reminds me of morse code.

Could you help me break it down so I can understand it better? Thank
you in advance for taking the time to teach!

The syntax is Replace(expression, find, replace [, start ] [, count ]
[, compare ] )

Replace(Replace([MaidenName], "(", ""), ")", "")

expression - ([MaidenName], why is there an open paren here and it's
matching closed paren after the replace?
find - "(" find the left paren
replace ""), replace it with nothing/blank
start - ")" I don't understand this. It seems to say "remove the
closing paren" but it's supposed to "start" somewhere
count - "") make all possible substitutions
compare - optional, not used

Can you substitute a letter (let's say the data is Miller, Mason,
Maxim...) and I want to remove all of the "M"s at the beginning of
each name.

Kathy R.

John W. Vinson wrote:
On Tue, 23 Feb 2010 19:14:35 -0500, "Kathy R."
wrote:

Hi Folks,

I have a field [MaidenName] that contains parenthesis, which I'd
like to remove.

Currently the data in the field looks like:
(Miller)

There's only 120 names that need to have the parenthesis stripped. By
the time I figure out how to do this in a query I could have done
it by hand, but I am determined to learn how to do this!

I've managed to strip the left paren with the following, but can't
figure out how to get rid of the right one:
MaidFix:
Mid([SP_MaidenName],InStr([SP_MaidenName],'(')+1,Len([SP_MaidenName])-InStr([SP_MaidenName],'
'))

Is there an easier way? If not, how do I change the statement
above to remove the right paren?

Your help is very much appreciated!

Kathy R.
In A2002 and later, you can use the builtin Replace() function.
Update MaidenName to

Replace(Replace([MaidenName], "(", ""), ")", "")

This will replace both parens with an empty string, converting
"(Miller)" to "Miller".