Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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?
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page