Query to strip text contained in parenthesis

[Charlotte]

I need to write a query that will strip parenthesis and the text
contained in the parenthesis. Example:
current: The cow (jumped over) the moon
new: The cow the moon

The below example I found will strip all text after the left
parenthesis"(", but I need to keep the text following the close ")"
parenthesis.

LongName: Left([Title],InStr([Title],"("),([Title],")")-1)

Charlotte

 

[Charlotte]

I need to write aquerythat willstripparenthesis and the text
contained in the parenthesis. Example:
current: The cow (jumped over) the moon
new: The cow the moon

The below example I found willstripall text after the left
parenthesis"(", but I need to keep the text following the close ")"
parenthesis.

LongName: Left([Title],InStr([Title],"("),([Title],")")-1)

Charlotte

Another quick note, if some of the records in my query don't contain
parenthesis, then the results get errors. Is there a way to prevent
this?

 

[fredg]

I need to write a query that will strip parenthesis and the text
contained in the parenthesis. Example:
current: The cow (jumped over) the moon
new: The cow the moon

The below example I found will strip all text after the left
parenthesis"(", but I need to keep the text following the close ")"
parenthesis.

LongName: Left([Title],InStr([Title],"("),([Title],")")-1)

Charlotte

RemoveText: Left([Title],InStr([Title],"(")-1) & Mid([Title],
InStr([Title],")")+2)

 

[Charlotte]

I need to write aquerythat willstripparenthesis and the text
contained in the parenthesis. Example:
current: The cow (jumped over) the moon
new: The cow the moon

The below example I found willstripall text after the left
parenthesis"(", but I need to keep the text following the close ")"
parenthesis.

LongName: Left([Title],InStr([Title],"("),([Title],")")-1)

Charlotte

RemoveText: Left([Title],InStr([Title],"(")-1) & Mid([Title],
InStr([Title],")")+2)

Wow. Thank you so much for your fast response. That worked! Do you
know how I can modify the query to not return #Error on records that
do not contain parenthesis?

 

[Conan Kelly]

Charlotte,

RemoveText:
Iif(InStr([Title]),"(")=0,[Title],Left([Title],InStr([Title],"(")-1) &
Mid([Title],InStr([Title],")")+2))

That will only look for a "(". If it can not find one, it will return the
original value of [Title]. If it does find one it will return [Title] with
everything between parentheses removed.

I can also adjust it to test for ")".......or "(" AND ")".......or "(" OR
")". What ever your needs require. Just let me know.

HTH,

Conan

I need to write aquerythat willstripparenthesis and the text
contained in the parenthesis. Example:
current: The cow (jumped over) the moon
new: The cow the moon

The below example I found willstripall text after the left
parenthesis"(", but I need to keep the text following the close ")"
parenthesis.

LongName: Left([Title],InStr([Title],"("),([Title],")")-1)

Charlotte

RemoveText: Left([Title],InStr([Title],"(")-1) & Mid([Title],
InStr([Title],")")+2)

Wow. Thank you so much for your fast response. That worked! Do you
know how I can modify the query to not return #Error on records that
do not contain parenthesis?

 

[Charlotte]

Charlotte,

RemoveText:
Iif(InStr([Title]),"(")=0,[Title],Left([Title],InStr([Title],"(")-1) &
Mid([Title],InStr([Title],")")+2))

That will only look for a "(". If it can not find one, it will return the
original value of [Title]. If it does find one it will return [Title] with
everything betweenparenthesesremoved.

I can also adjust it to test for ")".......or "(" AND ")".......or "(" OR
")". What ever your needs require. Just let me know.

HTH,

Conan

On Thu, 14 Feb 2008 14:23:49 -0800 (PST), Charlotte wrote:
I need to write aquerythat willstripparenthesis and the text
contained in the parenthesis. Example:
current: The cow (jumped over) the moon
new: The cow the moon
The below example I found willstripall text after the left
parenthesis"(", but I need to keep the text following the close ")"
parenthesis.
LongName: Left([Title],InStr([Title],"("),([Title],")")-1)
Charlotte
RemoveText: Left([Title],InStr([Title],"(")-1) & Mid([Title],
InStr([Title],")")+2)

Wow. Thank you so much for your fast response. That worked! Do you
know how I can modify thequeryto not return #Error on records that
do not contain parenthesis?

Thanks again for the fast response. For some reason, I'm getting an
invalid syntax on that expression.

 

[Charlotte]

Charlotte,

RemoveText:
Iif(InStr([Title]),"(")=0,[Title],Left([Title],InStr([Title],"(")-1) &
Mid([Title],InStr([Title],")")+2))

That will only look for a "(". If it can not find one, it will return the
original value of [Title]. If it does find one it will return [Title] with
everything betweenparenthesesremoved.

I can also adjust it to test for ")".......or "(" AND ")".......or "(" OR
")". What ever your needs require. Just let me know.

HTH,

Conan

news:33370946-974b-40aa-8c13-144a40811af6@v46g2000hsv.googlegroups.com...

On Thu, 14 Feb 2008 14:23:49 -0800 (PST), Charlotte wrote:
I need to write aquerythat willstripparenthesis and the text
contained in the parenthesis. Example:
current: The cow (jumped over) the moon
new: The cow the moon
The below example I found willstripall text after the left
parenthesis"(", but I need to keep the text following the close ")"
parenthesis.
LongName: Left([Title],InStr([Title],"("),([Title],")")-1)
Charlotte
RemoveText: Left([Title],InStr([Title],"(")-1) & Mid([Title],
InStr([Title],")")+2)
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Wow. Thank you so much for your fast response. That worked! Do you
know how I can modify thequeryto not return #Error on records that
do not contain parenthesis?

Thanks again for the fast response. For some reason, I'm getting an
invalid syntax on that expression.

Fixed. Just removed an extra close parenthesis. Again, thank you so
much for your help!
RemoveText: Iif(InStr([Title],"(")=0,
[Title],Left([Title],InStr([Title],"(")-1) &
Mid([Title],InStr([Title],")")+2))

 

[Conan Kelly]

Charlotte,

I'm sorry. I just typed that in to my response. Didn't test it in Access.
I got 1 too many parentheses in there. Try this:



RemoveText:
Iif(InStr([Title],"(")=0,[Title],Left([Title],InStr([Title],"(")-1) &
Mid([Title],InStr([Title],")")+2))



(make sure all of that is on one line)

In case you were wondering where my mistake was, it was near the biginning:

RemoveText: Iif(InStr([Title]),"(")=0...

Should have been

RemoveText: Iif(InStr([Title],"(")=0...

I had a closing (right) parentheses just after [Title].

Now that syntax should be correct, but I have not tested it. I think that
it will work for you, but let me know if it is still causing problems.

HTH,

Conan

Charlotte,

RemoveText:
Iif(InStr([Title]),"(")=0,[Title],Left([Title],InStr([Title],"(")-1) &
Mid([Title],InStr([Title],")")+2))

That will only look for a "(". If it can not find one, it will return
the
original value of [Title]. If it does find one it will return [Title]
with
everything betweenparenthesesremoved.

I can also adjust it to test for ")".......or "(" AND ")".......or "(" OR
")". What ever your needs require. Just let me know.

HTH,

Conan

On Thu, 14 Feb 2008 14:23:49 -0800 (PST), Charlotte wrote:
I need to write aquerythat willstripparenthesis and the text
contained in the parenthesis. Example:
current: The cow (jumped over) the moon
new: The cow the moon

The below example I found willstripall text after the left
parenthesis"(", but I need to keep the text following the close ")"
parenthesis.

LongName: Left([Title],InStr([Title],"("),([Title],")")-1)

Charlotte

RemoveText: Left([Title],InStr([Title],"(")-1) & Mid([Title],
InStr([Title],")")+2)

Wow. Thank you so much for your fast response. That worked! Do you
know how I can modify thequeryto not return #Error on records that
do not contain parenthesis?

Thanks again for the fast response. For some reason, I'm getting an
invalid syntax on that expression.

 

[Charlotte]

Charlotte,

I'm sorry. I just typed that in to my response. Didn't test it inAccess.
I got 1 too many parentheses in there. Try this:

RemoveText:
Iif(InStr([Title],"(")=0,[Title],Left([Title],InStr([Title],"(")-1) &
Mid([Title],InStr([Title],")")+2))

(make sure all of that is on one line)

In case you were wondering where my mistake was, it was near the biginning:

RemoveText: Iif(InStr([Title]),"(")=0...

Should have been

RemoveText: Iif(InStr([Title],"(")=0...

I had a closing (right) parentheses just after [Title].

Now that syntax should be correct, but I have not tested it. I think that
it will work for you, but let me know if it is still causing problems.

HTH,

Conan

Charlotte,
RemoveText:
Iif(InStr([Title]),"(")=0,[Title],Left([Title],InStr([Title],"(")-1) &
Mid([Title],InStr([Title],")")+2))
That will only look for a "(". If it can not find one, it will return
the
original value of [Title]. If it does find one it will return [Title]
with
everything betweenparenthesesremoved.
I can also adjust it to test for ")".......or "(" AND ")".......or "(" OR
")". What ever your needs require. Just let me know.
HTH,
Conan

On Thu, 14 Feb 2008 14:23:49 -0800 (PST),Charlottewrote:
I need to write aquerythat willstripparenthesis and the text
contained in theparenthesis. Example:
current: The cow (jumped over) the moon
new: The cow the moon
The below example I found willstripall text after the left
parenthesis"(", but I need to keep the text following the close ")"
parenthesis.
LongName: Left([Title],InStr([Title],"("),([Title],")")-1)
Charlotte
RemoveText: Left([Title],InStr([Title],"(")-1) & Mid([Title],
InStr([Title],")")+2)
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Wow. Thank you so much for your fast response. That worked! Do you
know how I can modify thequeryto not return #Error on records that
do not containparenthesis?

Thanks again for the fast response. For some reason, I'm getting an
invalid syntax on that expression.

That does work. Thank you. However, there are some records where I
have several sets of parenthesis. Example:
The (cow) jumped (over) the moon.
The expression only removes the first set of parenthesis (cow). Is
there a way we can get it to remove all sets of parenthesis? Sorry
that I should have mentioned this requirement in the beginning.

 

[fredg]

Charlotte,

I'm sorry. I just typed that in to my response. Didn't test it inAccess.
I got 1 too many parentheses in there. Try this:

RemoveText:
Iif(InStr([Title],"(")=0,[Title],Left([Title],InStr([Title],"(")-1) &
Mid([Title],InStr([Title],")")+2))

(make sure all of that is on one line)

In case you were wondering where my mistake was, it was near the biginning:

RemoveText: Iif(InStr([Title]),"(")=0...

Should have been

RemoveText: Iif(InStr([Title],"(")=0...

I had a closing (right) parentheses just after [Title].

Now that syntax should be correct, but I have not tested it. I think that
it will work for you, but let me know if it is still causing problems.

HTH,

Conan

On Feb 14, 6:29 pm, "Conan Kelly"
Charlotte,

RemoveText:
Iif(InStr([Title]),"(")=0,[Title],Left([Title],InStr([Title],"(")-1) &
Mid([Title],InStr([Title],")")+2))

That will only look for a "(". If it can not find one, it will return
the
original value of [Title]. If it does find one it will return [Title]
with
everything betweenparenthesesremoved.

I can also adjust it to test for ")".......or "(" AND ")".......or "(" OR
")". What ever your needs require. Just let me know.




On Thu, 14 Feb 2008 14:23:49 -0800 (PST),Charlottewrote:
I need to write aquerythat willstripparenthesis and the text
contained in theparenthesis. Example:
current: The cow (jumped over) the moon
new: The cow the moon

The below example I found willstripall text after the left
parenthesis"(", but I need to keep the text following the close ")"
parenthesis.

LongName: Left([Title],InStr([Title],"("),([Title],")")-1)

Charlotte

RemoveText: Left([Title],InStr([Title],"(")-1) & Mid([Title],
InStr([Title],")")+2)

Wow. Thank you so much for your fast response. That worked! Do you
know how I can modify thequeryto not return #Error on records that
do not containparenthesis?

Thanks again for the fast response. For some reason, I'm getting an
invalid syntax on that expression.

That does work. Thank you. However, there are some records where I
have several sets of parenthesis. Example:
The (cow) jumped (over) the moon.
The expression only removes the first set of parenthesis (cow). Is
there a way we can get it to remove all sets of parenthesis? Sorry
that I should have mentioned this requirement in the beginning.

Yes, you certainly left out a bit of important information.

You'll need to use a User Defined function.

Copy and paste the below code into a module:

Public Function RemoveStrings(strString As String) As String
Dim strNew As String
Dim intX As Integer
intX = InStr(strString, "(")
Do While intX > 0
strNew = strNew & Left(strString, intX - 1)
strString = Mid(strString, intX + 1)
strString = Mid(strString, InStr(strString, ")") + 2)
intX = InStr(strString, "(")
Loop
RemoveStrings = strNew & strString

End Function
__________________

You can call it from a query, using:
NewText:RemoveStrings([FieldName])

 

[fredg]

Charlotte,

I'm sorry. I just typed that in to my response. Didn't test it inAccess.
I got 1 too many parentheses in there. Try this:

RemoveText:
Iif(InStr([Title],"(")=0,[Title],Left([Title],InStr([Title],"(")-1) &
Mid([Title],InStr([Title],")")+2))

(make sure all of that is on one line)

In case you were wondering where my mistake was, it was near the biginning:

RemoveText: Iif(InStr([Title]),"(")=0...

Should have been

RemoveText: Iif(InStr([Title],"(")=0...

I had a closing (right) parentheses just after [Title].

Now that syntax should be correct, but I have not tested it. I think that
it will work for you, but let me know if it is still causing problems.

HTH,

Conan

On Feb 14, 6:29 pm, "Conan Kelly"
Charlotte,

RemoveText:
Iif(InStr([Title]),"(")=0,[Title],Left([Title],InStr([Title],"(")-1) &
Mid([Title],InStr([Title],")")+2))

That will only look for a "(". If it can not find one, it will return
the
original value of [Title]. If it does find one it will return [Title]
with
everything betweenparenthesesremoved.

I can also adjust it to test for ")".......or "(" AND ")".......or "(" OR
")". What ever your needs require. Just let me know.




On Thu, 14 Feb 2008 14:23:49 -0800 (PST),Charlottewrote:
I need to write aquerythat willstripparenthesis and the text
contained in theparenthesis. Example:
current: The cow (jumped over) the moon
new: The cow the moon

The below example I found willstripall text after the left
parenthesis"(", but I need to keep the text following the close ")"
parenthesis.

LongName: Left([Title],InStr([Title],"("),([Title],")")-1)

Charlotte

RemoveText: Left([Title],InStr([Title],"(")-1) & Mid([Title],
InStr([Title],")")+2)

Wow. Thank you so much for your fast response. That worked! Do you
know how I can modify thequeryto not return #Error on records that
do not containparenthesis?

Thanks again for the fast response. For some reason, I'm getting an
invalid syntax on that expression.

That does work. Thank you. However, there are some records where I
have several sets of parenthesis. Example:
The (cow) jumped (over) the moon.
The expression only removes the first set of parenthesis (cow). Is
there a way we can get it to remove all sets of parenthesis? Sorry
that I should have mentioned this requirement in the beginning.

I had a problem sending this a moment ago.
Perhaps this one will go through.

Yes, you certainly left out a bit of important information.

You'll need to use a User Defined function.

Copy and paste the below code into a module:

Public Function RemoveStrings(strString As String) As String
Dim strNew As String
Dim intX As Integer
intX = InStr(strString, "(")
Do While intX > 0
strNew = strNew & Left(strString, intX - 1)
strString = Mid(strString, intX + 1)
strString = Mid(strString, InStr(strString, ")") + 2)
intX = InStr(strString, "(")
Loop
RemoveStrings = strNew & strString

End Function
__________________

You can call it from a query, using:
NewText:RemoveStrings([FieldName])

 

[Charlotte]

Charlotte,
I'm sorry. I just typed that in to my response. Didn't test it inAccess.
I got 1 too many parentheses in there. Try this:
RemoveText:
Iif(InStr([Title],"(")=0,[Title],Left([Title],InStr([Title],"(")-1) &
Mid([Title],InStr([Title],")")+2))
(make sure all of that is on one line)
In case you were wondering where my mistake was, it was near the biginning:
RemoveText: Iif(InStr([Title]),"(")=0...
Should have been
RemoveText: Iif(InStr([Title],"(")=0...
I had a closing (right) parentheses just after [Title].
Now that syntax should be correct, but I have not tested it. I think that
it will work for you, but let me know if it is still causing problems.
HTH,
Conan

On Feb 14, 6:29 pm, "Conan Kelly"
Charlotte,
RemoveText:
Iif(InStr([Title]),"(")=0,[Title],Left([Title],InStr([Title],"(")-1) &
Mid([Title],InStr([Title],")")+2))
That will only look for a "(". If it can not find one, it will return
the
original value of [Title]. If it does find one it will return [Title]
with
everything betweenparenthesesremoved.
I can also adjust it to test for ")".......or "(" AND ")".......or "(" OR
")". What ever your needs require. Just let me know.
HTH,
Conan

On Thu, 14 Feb 2008 14:23:49 -0800 (PST),Charlottewrote:
I need to write aquerythat willstripparenthesis and the text
contained in theparenthesis. Example:
current: The cow (jumped over) the moon
new: The cow the moon
The below example I found willstripall text after the left
parenthesis"(", but I need to keep the text following the close ")"
parenthesis.
LongName: Left([Title],InStr([Title],"("),([Title],")")-1)
Charlotte
RemoveText: Left([Title],InStr([Title],"(")-1) & Mid([Title],
InStr([Title],")")+2)
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Wow. Thank you so much for your fast response. That worked! Do you
know how I can modify thequeryto not return #Error on records that
do not containparenthesis?
Thanks again for the fast response. For some reason, I'm getting an
invalid syntax on that expression.

That does work. Thank you. However, there are some records where I
have several sets of parenthesis. Example:
The (cow) jumped (over) the moon.
The expression only removes the first set of parenthesis (cow). Is
there a way we can get it to remove all sets of parenthesis? Sorry
that I should have mentioned this requirement in the beginning.

I had a problem sending this a moment ago.
Perhaps this one will go through.

Yes, you certainly left out a bit of important information.

You'll need to use a User Defined function.

Copy and paste the below code into a module:

Public Function RemoveStrings(strString As String) As String
Dim strNew As String
Dim intX As Integer
intX = InStr(strString, "(")
Do While intX > 0
strNew = strNew & Left(strString, intX - 1)
strString = Mid(strString, intX + 1)
strString = Mid(strString, InStr(strString, ")") + 2)
intX = InStr(strString, "(")
Loop
RemoveStrings = strNew & strString

End Function
__________________

You can call it from a query, using:
NewText:RemoveStrings([FieldName])

Perfect! Thank you so much. I'm not much of a programmer, so I
couldn't have done this on my own. Thank you again!

 

[Charlotte]

Charlotte,
I'm sorry. I just typed that in to my response. Didn't test it inAccess.
I got 1 too many parentheses in there. Try this:
RemoveText:
Iif(InStr([Title],"(")=0,[Title],Left([Title],InStr([Title],"(")-1) &
Mid([Title],InStr([Title],")")+2))
(make sure all of that is on one line)
In case you were wondering where my mistake was, it was near the biginning:
RemoveText: Iif(InStr([Title]),"(")=0...
Should have been
RemoveText: Iif(InStr([Title],"(")=0...
I had a closing (right) parentheses just after [Title].
Now that syntax should be correct, but I have not tested it. I think that
it will work for you, but let me know if it is still causing problems.
HTH,
Conan

On Feb 14, 6:29 pm, "Conan Kelly"
Charlotte,
RemoveText:
Iif(InStr([Title]),"(")=0,[Title],Left([Title],InStr([Title],"(")-1) &
Mid([Title],InStr([Title],")")+2))
That will only look for a "(". If it can not find one, it will return
the
original value of [Title]. If it does find one it will return [Title]
with
everything betweenparenthesesremoved.
I can also adjust it to test for ")".......or "(" AND ")".......or "(" OR
")". What ever your needs require. Just let me know.
HTH,
Conan

On Thu, 14 Feb 2008 14:23:49 -0800 (PST),Charlottewrote:
I need to write aquerythat willstripparenthesis and the text
contained in theparenthesis. Example:
current: The cow (jumped over) the moon
new: The cow the moon
The below example I found willstripall text after the left
parenthesis"(", but I need to keep the text following the close ")"
parenthesis.
LongName: Left([Title],InStr([Title],"("),([Title],")")-1)
Charlotte
RemoveText: Left([Title],InStr([Title],"(")-1) & Mid([Title],
InStr([Title],")")+2)
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Wow. Thank you so much for your fast response. That worked! Do you
know how I can modify thequeryto not return #Error on records that
do not containparenthesis?
Thanks again for the fast response. For some reason, I'm getting an
invalid syntax on that expression.

That does work. Thank you. However, there are some records where I
have several sets of parenthesis. Example:
The (cow) jumped (over) the moon.
The expression only removes the first set of parenthesis (cow). Is
there a way we can get it to remove all sets of parenthesis? Sorry
that I should have mentioned this requirement in the beginning.

I had a problem sending this a moment ago.
Perhaps this one will go through.

Yes, you certainly left out a bit of important information.

You'll need to use a User Defined function.

Copy and paste the below code into a module:

Public Function RemoveStrings(strString As String) As String
Dim strNew As String
Dim intX As Integer
intX = InStr(strString, "(")
Do While intX > 0
strNew = strNew & Left(strString, intX - 1)
strString = Mid(strString, intX + 1)
strString = Mid(strString, InStr(strString, ")") + 2)
intX = InStr(strString, "(")
Loop
RemoveStrings = strNew & strString

End Function
__________________

You can call it from a query, using:
NewText:RemoveStrings([FieldName])

Perfect! Thank you so much. I'm not much of a programmer, so I
couldn't have done this on my own. Thank you again!