Access 2000: Update all fields in a table

[judgehopkins]

How do I make this update query work?

UPDATE tblOldData 
SET SellerFN = Replace(Trim(StrConv([SellerFN],3)),"  "," ");

I imported a database into Access 2000. There is only one table, consisting of 12 text fields and two date fields.

Not every field in the table has information in it, i.e., there are a lot of null or empty fields. There are lots of double and triple spaces and inconsistent cases for the text (i.e., some all upper-case, some mixed case, etc.)

In other words, it is a mess.

I want to run an unpdate query to change the values in the table that need to be fixed.

I want to do three things: (1) trim leading and trailing spaces; (2) replace the double and triple spaces in the strings with a single space; and (3) capitalize the first letter of every word in the string.

How do I do this? The code above is not working. When I run the query on the 2350+ records, I get the following error message:

Microsoft Access didn't update 1747 records due to a type conversion failure....

Thanks!

"The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?" Anthony Burgess, A Mouthful of Air

 

[dickiebird]

IMHO, you're confusing Worksheet function REPLACE with SQL
statements.
UIM, your SQL should use LTRIM and RTRIM to remove spurious spaces.

Dickie Bird (-)))