Replace with blank

[Guest]

I'm trying to replace "(any text)" in the company name field with a blank. I
tried using the following but it doesn't work.

Sample Data: Bridgeport Mortgage Company (San Fran)

In my update query - Replace([Company Name],"(*)","")
I get no changes...

As an example - this seems to work...
Replace([DBA],"(San Fran)","blank")
I get Bridgeport Mortgage Company blank....

This is great but I need to remove any parantheses and leave the space
blank. Why won't the query recognize the wildcard?

 

[aaron.kempf]

you've got to write your own funciton to do that
i would just take the left of the field, to the INSTR where you find
the ( character.. and then rtrim it after doing that so you kill the
trailing spaces

 

[Dirk Goldgar]

I'm trying to replace "(any text)" in the company name field with a
blank. I tried using the following but it doesn't work.

Sample Data: Bridgeport Mortgage Company (San Fran)

In my update query - Replace([Company Name],"(*)","")
I get no changes...

As an example - this seems to work...
Replace([DBA],"(San Fran)","blank")
I get Bridgeport Mortgage Company blank....

This is great but I need to remove any parantheses and leave the space
blank. Why won't the query recognize the wildcard?

The Replace function doesn't support wildcards or regular expressions.
For something like this, you'll need to write your own function. Here's
a quickie -- not necessarily the most efficient, but it ought to work:

'----- start of code -----
Function ReplaceParentheticals( _
pvarSource As Variant, _
pstrReplacement As String) _
As Variant

' Replace any parenthetical expression in <pvarSource>
' with <pstrReplacement>. The enclosing parentheses will also
' be removed.

Dim strWork As String
Dim lngOpen As Long
Dim lngClose As Long

If IsNull(pvarSource) Then Exit Function

strWork = pvarSource

Do
lngOpen = InStr(strWork, "(")

If lngOpen > 0 Then
lngClose = InStr(lngOpen + 1, strWork, ")")
If lngClose = 0 Then
' No closing parenthesis; nothing more to do.
Exit Do
Else
strWork = _
Left(strWork, lngOpen - 1) & _
pstrReplacement & _
Mid(strWork, lngClose + 1)
End If
End If

Loop Until lngOpen = 0

ReplaceParentheticals = strWork

End Function
'----- end of code -----

Your query expression would then be

ReplaceParentheticals([Company Name],"")

You may want to trim the result to get rid of trailing spaces:

Trim(ReplaceParentheticals([Company Name],""))

 

[Guest]

First Back up your data

Try this update query

UPDATE TableName SET TableName.[FieldName]= iif
(instr([FieldName],"(")=True,Left([FieldName],instr([FieldName],"(")-1),[FieldName])

 

[Guest]

Thanks Ofer!!! You "nailed" it.

Crystal

First Back up your data

Try this update query

UPDATE TableName SET TableName.[FieldName]= iif
(instr([FieldName],"(")=True,Left([FieldName],instr([FieldName],"(")-1),[FieldName])
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck

I'm trying to replace "(any text)" in the company name field with a blank. I
tried using the following but it doesn't work.

Sample Data: Bridgeport Mortgage Company (San Fran)

In my update query - Replace([Company Name],"(*)","")
I get no changes...

As an example - this seems to work...
Replace([DBA],"(San Fran)","blank")
I get Bridgeport Mortgage Company blank....

This is great but I need to remove any parantheses and leave the space
blank. Why won't the query recognize the wildcard?