This discussion is closed.
starnold
Badges: 0
Rep:
?
#1
Report Thread starter 16 years ago
#1
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
1
PQ
Badges: 21
Rep:
?
#2
Report 16 years ago
#2
(Original post by 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
0
starnold
Badges: 0
Rep:
?
#3
Report Thread starter 16 years ago
#3
There are more than 65K records so I can't use excel???
0
PQ
Badges: 21
Rep:
?
#4
Report 16 years ago
#4
(Original post by 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.
0
serendipity
Badges: 9
Rep:
?
#5
Report 16 years ago
#5
My goodness it's amazing the number of things i just don't understand...
1
PQ
Badges: 21
Rep:
?
#6
Report 16 years ago
#6
(Original post by 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.
0
PQ
Badges: 21
Rep:
?
#7
Report 16 years ago
#7
(Original post by 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
0
Camford
Badges: 12
Rep:
?
#8
Report 16 years ago
#8
Use input mask.
1
lou p lou
Badges: 2
Rep:
?
#9
Report 16 years ago
#9
(Original post by serendipity)
My goodness it's amazing the number of things i just don't understand...
amazing... i was thinking exactly the same...

lou xxx
0
X
new posts
Back
to top
Latest
My Feed

See more of what you like on
The Student Room

You can personalise what you see on TSR. Tell us a little about yourself to get started.

Personalise

If you do not get the A-level grades you want this summer, what is your likely next step?

Take autumn exams (19)
41.3%
Take exams next summer (11)
23.91%
Change uni choice through clearing (11)
23.91%
Apply to uni next year instead (4)
8.7%
I'm not applying to university (1)
2.17%

Watched Threads

View All