Update Query

Anyone know how to convert data to Upper case.

I have a table that has towns in and I need to conver the first letter of each town to upper case

Reply 1

Clearing and Applications Advisor

starnold

Anyone know how to convert data to Upper case.

I have a table that has towns in and I need to conver the first letter of each town to upper case

Bung it into excel and type =proper(A1) in the column next to it then re-import

Reply 2

starnold

There are more than 65K records so I can't use excel???

Reply 3

Clearing and Applications Advisor

starnold

There are more than 65K records so I can't use excel???

65K unique records or 65K records?

If they aren't unique then extract the Town name into a smaller table and pull in the town name to the main table using town code.

Then extract the contents of the town table reformat in excel and reinsert it into the database.

If they are unique then you're stuffed without writing a very complicated update query which splits the left hand character out of the town name converts it into upper case and then concatanates the 2 sections of the town name back together.

Reply 4

serendipity

My goodness it's amazing the number of things i just don't understand... :frown:

Reply 5

Clearing and Applications Advisor

starnold

There are more than 65K records so I can't use excel???

Another alternative if there are only a limited number of unique records (and you can't be bothered restructuring your database to reformat them) would be to creating a query pulling out every unique town name and do a find and replace in your table to correct each one in turn - but obviously if there are more than 20 or so this would take quite a while.

Reply 6

Clearing and Applications Advisor

starnold

Anyone know how to convert data to Upper case.

I have a table that has towns in and I need to conver the first letter of each town to upper case

I've had a fiddle and it is possible within Access (but I'd suggest you try it on a test field first) using

"=Left(UCase([TableName]![Town]),1) & LCase (Right([TableName]![Town],(Len([TableName]![Town])-1)))" in the Update To box