Changing case of data already in access tables

[RichardP1978]

My database is now live within our office and is working fine.

The databases which I have imported the data from, where done by my dad, who being old fashioned, used CAPS for everything he typed. So for quite alot of the data, its all caps, and it would be great, but not essential if we could revert the text to normal sentences.

Is there any trick or code that I could run that would go through certain tables and reformat the text in to a better format??

 

[Brianwarnock]

You could run an update query against thr table. For each field you want to change the update would be StrConv(fldname,3) The 3 identifies vbPropercase but the key word doesn't seem to work in queries, 2 is lowercase if you prefer.

Brian

 

[RichardP1978]

Thanks for that Brian, at least I know there is a way.

I am at a bit of a loss on how to do this, I have never run a update query. How would I run this?

 

[Brianwarnock]

Having got as far as the query grid with your table selected click on Query on the menu bar and then select update from the list. The query grid will change slightly and i think from that point all will be clear, if not come back.

Oh I should point out that you cannot preview the change, when you select view it shows which fields it is going to change , not what it is going to change them to, so you might like to take a copy of your table before starting, tho' you can always change everything back to capitals.

Brian

 

[RichardP1978]

I get as far as the update option fine, and enter StrConv(fldname,3) in the Update To section.

I have tried replacing fldname with the name of the field, but all I get is what is typed in there.

It is probably something so simple its untrue, but I really cant get it to work. I am in awe of everyone elses skill with access. lol

 

[RichardP1978]

Ahhh, just got it, wasnt using [] either side of field name.

With it set to 3, the text is Always Like This For Capitalisation. Is there a number that would only capitalise the first letter of a sentence? If not, it might look better all round to go with all lowercase characters, but at least I have sussed it now.

Thanks Brian, its been a help.

 

[Brianwarnock]

The short answer is no, I was not thinking of sentences, but things like names and addresses or single items. To do it for sentences would require code and look to capitalise the first letter following certain punctuation eg !.?and a space, don't know if any exists, you could try searching the forum.

For your information/education many functions that you can use in queries are only described in VBA help, hit Alt+F11 to open the editor and select its help, enter strconv in the index to find info on case.

Brian