Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Replace with blank

Discussion in 'Information Technology' started by mktg@wfi, Nov 4, 2005.

Tags:
  1. mktg@wfi

    mktg@wfi
    Expand Collapse
    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?
     
  2. Loading...


  3. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "mktg@wfi" <mktgwfi@discussions.microsoft.com> wrote in message
    news:F055C31F-9F2A-4BC4-8450-F2BEED10314D@microsoft.com
    > 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],""))

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  4. Ofer

    Ofer
    Expand Collapse
    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])
    --
    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



    "mktg@wfi" wrote:

    > 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?
    >
     

Share This Page