Find/Replace using an update query

[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.

 

[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")

 

[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

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

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.

 

[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.

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

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

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.

 

[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

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.

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

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



:

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.

 

[Douglas J Steele]

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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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.

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

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



:

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.

 

[Guest]

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

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

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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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.

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



:

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



:

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.

 

[Douglas J Steele]

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

(no e-mails, please!)

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

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

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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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.

:

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

it
will

stay saved for a longer time, so other can benefit from it.

Good luck



:

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")

way, it
will

stay saved for a longer time, so other can benefit from it.

Good luck



:

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.

 

[Guest]

Good solution

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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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.

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



:

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



:

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.

 

[Mustafa ÇALIK]

It is a test message

 

[Van T. Dinh]

I am not sure which conversion you referred to but check Access VB Help on
the functions StrConv(), UCase() and LCase() ...

 

[Pieter Wijnen]

I think Replace should do it

ie

UPDATE MyTable Set MyField = Replace([MyField],"Llc","LLC")

should do it

Pieter

 

[JustALittleHelp]

Is it possible to add more criteria? I have to modify an address field
containing long form address suffixes to short form, i.e., Avenue to Ave,
Drive to Dr, Street to St, etc. and will have several of these pass through
the field.
Thanks in advance