Changing string formatting and updating database

[shivboy]

Hi,

I have a database with registration numbers and corresponding names. There are 2 columns:

1. Reg_No - Numeric
2. Name - Char

The data entry of names has been done wherein some names have the formatting like john Peters, JOHN PETERS, jOhn peters etc. Is there a way I can make all the entries in the format John Peters without have to manually changing it?

Please help. Thanks in advance.

Peace,
Shiv

 

[maxmangion]

strconv([fieldname], vbProperCase)

 

[shivboy]

hi maxmangion,

thanx for replying, but i would be grateful if u cud give me the whole query. this is so because, when i passed the query in the Design View, i passed it as UPDATE * strConv(ApplicantName,vbProperCase) and saved the query. When i executed it, a dialog box first came asking for a value under "UPDATE" wherein i gave table name, then it asked for a value under "vbProperCase" for which i dont know what value i shud be entering. i would be grateful if u cud show me how i could update the table with the strings in the format "John Peters". Furthermore, this query passed seemed to require me to update each cell in the column one by one, is there a way i can apply the same format to whole of the column? Please help me, i'll be really grateful.

Peace,
Shiv

 

[maxmangion]

the above example shouldn't be done in a query, because it is a piece of VBA code, and should be done in the visual basic editor.

 

[shivboy]

Thanx for replying again, but I have no clue as to how to go about doing that. Is there a way I can do within Access using SQL? I'm new to all this, and I would be grateful if you could suggest a way by which I could find a solution to the problem.

 

[Pat Hartman]

The query should look like:
UPDATE YourTable SET YourField = StrConv([YourField],3);

I used the literal value 3 rather than the constant vbProperCase because SQL doesn't always recognize VB constants.

As with ANY bulk update. The wisest course is to backup PRIOR to testing.