Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    May 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Wild Card Replacement (2000 SR1)

    I need to delete some "*" trailing a text field in Access to allow me to create a relationship for cross-referencing the data.

    The Field can contain 1 of 4 types of data: SBRTE** or AGPF** or SBUP or BGAIF

    Basically how do I remove the double star without treating it acting as a wildcard and use an automatic repeatable process. This can be any type of query, I'm hoping to avoid delving in to VBA. The letters are an individual code and are different in each record.

    Any ideas?

    I hope thats clear.

    graliv
    (UK)

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Wild Card Replacement (2000 SR1)

    Here's an example of a way to do it in a query:

    UPDATE Customers SET Customers.CompanyName = Left([CompanyName],InStr([CompanyName],"*")-1) & Mid([CompanyName],InStr([CompanyName],"*")+1)
    WHERE (((InStr([CompanyName],"*")>0)=True));

    You aren't using the asterisk as a wildcard here, you're using it as a literal. This particular query won't handle multiple asterisks in one pass, but if you ran it again, it would find the next asterisk and remove it. Access 2000 also has a replace function, but it can't always be used properly in a query, so if you want to avoid code, just running this kind of query until it doesn't find any more records to update will do the trick.
    Charlotte

  3. #3
    New Lounger
    Join Date
    May 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wild Card Replacement (2000 SR1)

    ThankYou!!!! As usual I'm humbled by the skill and knowledge displayed by the loungers.

    See ya next time my brain packs up.

    Graliv

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Wild Card Replacement (2000 SR1)

    Hi Charlotte,
    Just did a search and found your post, which answered my question on asterisks.
    Thanks,
    HH

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Wild Card Replacement (2000 SR1)

    The original post was about Access 2000. If you're using a later version, you can use the Replace function in queries; it will replace all asterisks in one go. Charlotte's example could be changed to
    <code>
    UPDATE Customers SET Customers.CompanyName = Replace([CompanyName],"*","")
    WHERE (((InStr([CompanyName],"*")>0)=True));
    </code>
    or even
    <code>
    UPDATE Customers SET Customers.CompanyName = Replace([CompanyName],"*","");</code>

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Wild Card Replacement (2000 SR1)

    Great!,
    I'll bookmark this one. Never knew about Replace, which I know I will use in the future. This forum continues to be my favorite for all things Office and Windows.
    Best,
    HH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •