Capitalize first letter

[mlopes1]

I found many posts close to what I need but not quite. I have a column in a table with about 10,000 records. The field holds product descriptions that can have anywhere from 1 to 25 words in them. By default, when I import the list, everything comes in CAPS. Example: WIDGET FROM MEXICO

I would like to write a module that runs on a button. It would open specified table (Product_Table) and go through each record, changing the text to have only the first letter of each word Capitalized. Or in the example above: Widget From Mexico.

I saw the post with code for changing the very first letter of the record, but how do I check and replace for each word in a record?


Thank you as always,

Marco

 

[Mile-O]

Lookup the StrConv() function and change the records to vbPropercase.

 

[mlopes1]

Thank you, that was what I needed but it also brought me straight up to my next road block. How do I move through each record in the table and record its value?


Is there an easy way to do this? I am thinking explaining my overall goal may clarify this. I have one table (CriteriaTable) that has 2 columns. Column one is a list of 100's of words. Column 2 is a list of what column one should be converted too. Example: Column One: Mexico Column Two: Mexicana
Japan Japone

I then have ProductTable with the product records I described before. I am now trying to write a procedure that finds and replaces using all the values in CriteriaTable and makes those changes in each record of ProductsTable. Conceptually I am thinking something like:

For each record in ProductsTable
For each record in CriteriaTable
searchcriteria = CriteriaTable.column1.currentrecord
replacecriteria = CriteriaTable.column2.currentrecord
with ProductsTable.currentrecord
find(searchcriteria)
replace(replacecriteria)
end with
NextRecord in CriteriaTable
NextRecord in ProductsTable

Any ideas on the code/functions for this? Or more likely, ideas for a smoother way of doing this? My goal is that I can just add to the criteria table in the future if necessary and have it run through the Products table and find-replace everything.

Thanks as always,

marco

 

[Rich]

Use an UpdateQuery

 

[mlopes1]

Could you please explain how that would work, given my situation. I do not know nearly enough about Access to take that answer and run with it. Thanks.

 

[ghudson]

The attached picture will show you how the design of the update query would look. My table name is tblEmployees and the field name that I am converting to proper case is Names. Check the help files for the StrConv function.

HTH