Welcome to SPN

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

Sign Up Now!

Find/Replace using an update query

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

  1. mktg@wfi

    mktg@wfi
    Expand Collapse
    Guest

    I've converted my company names from ALL CAPS to Proper Case using an update
    query. Now I'm left with portions of records that need correcting. I want
    to use an update query to Find/Replace text within records such as:
    Find: Llc
    Replace with: LLC

    Find: Llp
    Replace with: LLP

    Any helpful hints as to how to do this using and update query. I keep
    replace the entire company name with LLC. I can't figure out how to properly
    use the correct wildcards.
     
  2. Loading...


  3. Ofer

    Ofer
    Expand Collapse
    Guest

    First Back up your data

    Then use update query with replace

    UPDATE TableName SET TableName.[FieldName]= Replace([FieldName],"Llc","LLc")

    UPDATE TableName SET TableName.[FieldName]= Replace([FieldName],"Llp","LLp")
    --
    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've converted my company names from ALL CAPS to Proper Case using an update
    > query. Now I'm left with portions of records that need correcting. I want
    > to use an update query to Find/Replace text within records such as:
    > Find: Llc
    > Replace with: LLC
    >
    > Find: Llp
    > Replace with: LLP
    >
    > Any helpful hints as to how to do this using and update query. I keep
    > replace the entire company name with LLC. I can't figure out how to properly
    > use the correct wildcards.
     
  4. Ofer

    Ofer
    Expand Collapse
    Guest

    I didn't put all letters in caps lock

    UPDATE TableName SET TableName.[FieldName]= Replace([FieldName],"Llc","LLC")

    UPDATE TableName SET TableName.[FieldName]= Replace([FieldName],"Llp","LLP")

    Again, back up first
    --
    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



    "Ofer" wrote:

    > First Back up your data
    >
    > Then use update query with replace
    >
    > UPDATE TableName SET TableName.[FieldName]= Replace([FieldName],"Llc","LLc")
    >
    > UPDATE TableName SET TableName.[FieldName]= Replace([FieldName],"Llp","LLp")
    > --
    > 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've converted my company names from ALL CAPS to Proper Case using an update
    > > query. Now I'm left with portions of records that need correcting. I want
    > > to use an update query to Find/Replace text within records such as:
    > > Find: Llc
    > > Replace with: LLC
    > >
    > > Find: Llp
    > > Replace with: LLP
    > >
    > > Any helpful hints as to how to do this using and update query. I keep
    > > replace the entire company name with LLC. I can't figure out how to properly
    > > use the correct wildcards.
     
  5. mktg@wfi

    mktg@wfi
    Expand Collapse
    Guest

    Great. Can I make mutliple udates to the same field in one update query? I
    tried to put both updates into one query like you said but I get an error.

    "Ofer" wrote:

    > I didn't put all letters in caps lock
    >
    > UPDATE TableName SET TableName.[FieldName]= Replace([FieldName],"Llc","LLC")
    >
    > UPDATE TableName SET TableName.[FieldName]= Replace([FieldName],"Llp","LLP")
    >
    > Again, back up first
    > --
    > 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
    >
    >
    >
    > "Ofer" wrote:
    >
    > > First Back up your data
    > >
    > > Then use update query with replace
    > >
    > > UPDATE TableName SET TableName.[FieldName]= Replace([FieldName],"Llc","LLc")
    > >
    > > UPDATE TableName SET TableName.[FieldName]= Replace([FieldName],"Llp","LLp")
    > > --
    > > 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've converted my company names from ALL CAPS to Proper Case using an update
    > > > query. Now I'm left with portions of records that need correcting. I want
    > > > to use an update query to Find/Replace text within records such as:
    > > > Find: Llc
    > > > Replace with: LLC
    > > >
    > > > Find: Llp
    > > > Replace with: LLP
    > > >
    > > > Any helpful hints as to how to do this using and update query. I keep
    > > > replace the entire company name with LLC. I can't figure out how to properly
    > > > use the correct wildcards.
     
  6. Ofer

    Ofer
    Expand Collapse
    Guest

    You should use them seperetly
    --
    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:

    > Great. Can I make mutliple udates to the same field in one update query? I
    > tried to put both updates into one query like you said but I get an error.
    >
    > "Ofer" wrote:
    >
    > > I didn't put all letters in caps lock
    > >
    > > UPDATE TableName SET TableName.[FieldName]= Replace([FieldName],"Llc","LLC")
    > >
    > > UPDATE TableName SET TableName.[FieldName]= Replace([FieldName],"Llp","LLP")
    > >
    > > Again, back up first
    > > --
    > > 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
    > >
    > >
    > >
    > > "Ofer" wrote:
    > >
    > > > First Back up your data
    > > >
    > > > Then use update query with replace
    > > >
    > > > UPDATE TableName SET TableName.[FieldName]= Replace([FieldName],"Llc","LLc")
    > > >
    > > > UPDATE TableName SET TableName.[FieldName]= Replace([FieldName],"Llp","LLp")
    > > > --
    > > > 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've converted my company names from ALL CAPS to Proper Case using an update
    > > > > query. Now I'm left with portions of records that need correcting. I want
    > > > > to use an update query to Find/Replace text within records such as:
    > > > > Find: Llc
    > > > > Replace with: LLC
    > > > >
    > > > > Find: Llp
    > > > > Replace with: LLP
    > > > >
    > > > > Any helpful hints as to how to do this using and update query. I keep
    > > > > replace the entire company name with LLC. I can't figure out how to properly
    > > > > use the correct wildcards.
     
  7. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    UPDATE TableName SET TableName.[FieldName]=
    Replace(Replace([FieldName],"Llc","LLC"), "Llp", "LLP")


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "mktg@wfi" <mktgwfi@discussions.microsoft.com> wrote in message
    news:AA425E9F-5C39-42EF-8AE6-E94D5A2D1829@microsoft.com...
    > Great. Can I make mutliple udates to the same field in one update query?

    I
    > tried to put both updates into one query like you said but I get an error.
    >
    > "Ofer" wrote:
    >
    > > I didn't put all letters in caps lock
    > >
    > > UPDATE TableName SET TableName.[FieldName]=

    Replace([FieldName],"Llc","LLC")
    > >
    > > UPDATE TableName SET TableName.[FieldName]=

    Replace([FieldName],"Llp","LLP")
    > >
    > > Again, back up first
    > > --
    > > 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
    > >
    > >
    > >
    > > "Ofer" wrote:
    > >
    > > > First Back up your data
    > > >
    > > > Then use update query with replace
    > > >
    > > > UPDATE TableName SET TableName.[FieldName]=

    Replace([FieldName],"Llc","LLc")
    > > >
    > > > UPDATE TableName SET TableName.[FieldName]=

    Replace([FieldName],"Llp","LLp")
    > > > --
    > > > 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've converted my company names from ALL CAPS to Proper Case using

    an update
    > > > > query. Now I'm left with portions of records that need correcting.

    I want
    > > > > to use an update query to Find/Replace text within records such as:
    > > > > Find: Llc
    > > > > Replace with: LLC
    > > > >
    > > > > Find: Llp
    > > > > Replace with: LLP
    > > > >
    > > > > Any helpful hints as to how to do this using and update query. I

    keep
    > > > > replace the entire company name with LLC. I can't figure out how to

    properly
    > > > > use the correct wildcards.
     
  8. DK

    DK
    Expand Collapse
    Guest

    Try this. This will update the field and put all letters to uppercase

    UPDATE TableName SET [TableName].[Fieldname] = UCase([FieldName]);


    "Douglas J Steele" wrote:

    > UPDATE TableName SET TableName.[FieldName]=
    > Replace(Replace([FieldName],"Llc","LLC"), "Llp", "LLP")
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "mktg@wfi" <mktgwfi@discussions.microsoft.com> wrote in message
    > news:AA425E9F-5C39-42EF-8AE6-E94D5A2D1829@microsoft.com...
    > > Great. Can I make mutliple udates to the same field in one update query?

    > I
    > > tried to put both updates into one query like you said but I get an error.
    > >
    > > "Ofer" wrote:
    > >
    > > > I didn't put all letters in caps lock
    > > >
    > > > UPDATE TableName SET TableName.[FieldName]=

    > Replace([FieldName],"Llc","LLC")
    > > >
    > > > UPDATE TableName SET TableName.[FieldName]=

    > Replace([FieldName],"Llp","LLP")
    > > >
    > > > Again, back up first
    > > > --
    > > > 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
    > > >
    > > >
    > > >
    > > > "Ofer" wrote:
    > > >
    > > > > First Back up your data
    > > > >
    > > > > Then use update query with replace
    > > > >
    > > > > UPDATE TableName SET TableName.[FieldName]=

    > Replace([FieldName],"Llc","LLc")
    > > > >
    > > > > UPDATE TableName SET TableName.[FieldName]=

    > Replace([FieldName],"Llp","LLp")
    > > > > --
    > > > > 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've converted my company names from ALL CAPS to Proper Case using

    > an update
    > > > > > query. Now I'm left with portions of records that need correcting.

    > I want
    > > > > > to use an update query to Find/Replace text within records such as:
    > > > > > Find: Llc
    > > > > > Replace with: LLC
    > > > > >
    > > > > > Find: Llp
    > > > > > Replace with: LLP
    > > > > >
    > > > > > Any helpful hints as to how to do this using and update query. I

    > keep
    > > > > > replace the entire company name with LLC. I can't figure out how to

    > properly
    > > > > > use the correct wildcards.

    >
    >
    >
     
  9. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Sure it will, but that's not what the Original Poster wants.

    He/she started with text that was all capitals, and used StrConv to convert
    it to proper case. However, some of the text that was converted (proper
    abbreviations) should stay as upper case. There's no VBA function that's
    capable of doing that.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "DK" <DK@discussions.microsoft.com> wrote in message
    news:6AC3C1F6-BBBC-48C9-81F6-36DE7543335C@microsoft.com...
    > Try this. This will update the field and put all letters to uppercase
    >
    > UPDATE TableName SET [TableName].[Fieldname] = UCase([FieldName]);
    >
    >
    > "Douglas J Steele" wrote:
    >
    > > UPDATE TableName SET TableName.[FieldName]=
    > > Replace(Replace([FieldName],"Llc","LLC"), "Llp", "LLP")
    > >
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "mktg@wfi" <mktgwfi@discussions.microsoft.com> wrote in message
    > > news:AA425E9F-5C39-42EF-8AE6-E94D5A2D1829@microsoft.com...
    > > > Great. Can I make mutliple udates to the same field in one update

    query?
    > > I
    > > > tried to put both updates into one query like you said but I get an

    error.
    > > >
    > > > "Ofer" wrote:
    > > >
    > > > > I didn't put all letters in caps lock
    > > > >
    > > > > UPDATE TableName SET TableName.[FieldName]=

    > > Replace([FieldName],"Llc","LLC")
    > > > >
    > > > > UPDATE TableName SET TableName.[FieldName]=

    > > Replace([FieldName],"Llp","LLP")
    > > > >
    > > > > Again, back up first
    > > > > --
    > > > > 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
    > > > >
    > > > >
    > > > >
    > > > > "Ofer" wrote:
    > > > >
    > > > > > First Back up your data
    > > > > >
    > > > > > Then use update query with replace
    > > > > >
    > > > > > UPDATE TableName SET TableName.[FieldName]=

    > > Replace([FieldName],"Llc","LLc")
    > > > > >
    > > > > > UPDATE TableName SET TableName.[FieldName]=

    > > Replace([FieldName],"Llp","LLp")
    > > > > > --
    > > > > > 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've converted my company names from ALL CAPS to Proper Case

    using
    > > an update
    > > > > > > query. Now I'm left with portions of records that need

    correcting.
    > > I want
    > > > > > > to use an update query to Find/Replace text within records such

    as:
    > > > > > > Find: Llc
    > > > > > > Replace with: LLC
    > > > > > >
    > > > > > > Find: Llp
    > > > > > > Replace with: LLP
    > > > > > >
    > > > > > > Any helpful hints as to how to do this using and update query.

    I
    > > keep
    > > > > > > replace the entire company name with LLC. I can't figure out

    how to
    > > properly
    > > > > > > use the correct wildcards.

    > >
    > >
    > >
     
  10. Ofer

    Ofer
    Expand Collapse
    Guest

    Good solution


    "Douglas J Steele" wrote:

    > UPDATE TableName SET TableName.[FieldName]=
    > Replace(Replace([FieldName],"Llc","LLC"), "Llp", "LLP")
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "mktg@wfi" <mktgwfi@discussions.microsoft.com> wrote in message
    > news:AA425E9F-5C39-42EF-8AE6-E94D5A2D1829@microsoft.com...
    > > Great. Can I make mutliple udates to the same field in one update query?

    > I
    > > tried to put both updates into one query like you said but I get an error.
    > >
    > > "Ofer" wrote:
    > >
    > > > I didn't put all letters in caps lock
    > > >
    > > > UPDATE TableName SET TableName.[FieldName]=

    > Replace([FieldName],"Llc","LLC")
    > > >
    > > > UPDATE TableName SET TableName.[FieldName]=

    > Replace([FieldName],"Llp","LLP")
    > > >
    > > > Again, back up first
    > > > --
    > > > 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
    > > >
    > > >
    > > >
    > > > "Ofer" wrote:
    > > >
    > > > > First Back up your data
    > > > >
    > > > > Then use update query with replace
    > > > >
    > > > > UPDATE TableName SET TableName.[FieldName]=

    > Replace([FieldName],"Llc","LLc")
    > > > >
    > > > > UPDATE TableName SET TableName.[FieldName]=

    > Replace([FieldName],"Llp","LLp")
    > > > > --
    > > > > 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've converted my company names from ALL CAPS to Proper Case using

    > an update
    > > > > > query. Now I'm left with portions of records that need correcting.

    > I want
    > > > > > to use an update query to Find/Replace text within records such as:
    > > > > > Find: Llc
    > > > > > Replace with: LLC
    > > > > >
    > > > > > Find: Llp
    > > > > > Replace with: LLP
    > > > > >
    > > > > > Any helpful hints as to how to do this using and update query. I

    > keep
    > > > > > replace the entire company name with LLC. I can't figure out how to

    > properly
    > > > > > use the correct wildcards.

    >
    >
    >
     

Share This Page