Proper Case Update Query

[mike2246]

I have an older database that has been managed by several people now and most fields are all different, some upper/lower/proper.

For example how/where would I make the update query to change everything to proper case
Table - Customer
Column - Customer Name

Thanks

[Micron]

Check out strConv function. It will convert every first letter to upper case, so words that might not ordinarily be capitalized will be.

[mike2246]

Check out strConv function. It will convert every first letter to upper case, so words that might not ordinarily be capitalized will be.

I know that is the command, but not sure where or how to finish the command after strConv

[accesstos]

I have an older database that has been managed by several people now and most fields are all different, some upper/lower/proper.

For example how/where would I make the update query to change everything to proper case
Table - Customer
Column - Customer Name

Thanks

Hi Mike!

In a copy of the table Customer (say CustomerCopy) , run this query:

UPDATE CustomerCopy SET [Customer Name]= StrConv([Customer Name],3);

If has updated successfully, repeat it for the Customer table.

[mike2246]

Hi Mike!

In a copy of the table Customer (say CustomerCopy) , run this query:

UPDATE CustomerCopy SET [Customer Name]= StrConv([Customer Name],3);

If has updated successfully, repeat it for the Customer table.

That worked, Thanks! Now what do I do to the query to make it update multiple fields at once?

I tried the following but it didn't work (UPDATE CustomerCopy SET CustomerCopy.Customer_Name = StrConv([Customer_Name],3), CustomerCopy.City = [UPDATE CustomerCopy SET CustomerCopy].[City]=StrConv([City],3);

[June7]

UPDATE CustomerCopy SET CustomerCopy.Customer_Name = StrConv([Customer_Name],3), CustomerCopy.City = StrConv([City],3);

Just separate each update pair with comma.

[mike2246]

Thanks, got it working with multiple fields and tables. Can I run a separate query or add to this one that will do a find/replace? for example there's about 10 names the don't need to be proper case (Mmg should be MMG, Miu should be MIU, etc.)

[June7]

Might have to do a separate update action for each replace. If this is a one time process, just use Find/Replace tool from the ribbon.

Are those 3 letters the entire name?

[accesstos]

Thanks, got it working with multiple fields and tables. Can I run a separate query or add to this one that will do a find/replace? for example there's about 10 names the don't need to be proper case (Mmg should be MMG, Miu should be MIU, etc.)

You can use the IN() operator to specify the words that you want to convert to uppercase as follows:

UPDATE CustomerCopy SET [Customer Name]= StrConv([Customer Name],1) WHERE [Customer Name] IN("mmg", "miu", "otherName");