Turn on thread page Beta
    • Thread Starter
    Offline

    0
    ReputationRep:
    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
    • Very Important Poster
    • PS Reviewer
    Offline

    21
    ReputationRep:
    Very Important Poster
    PS Reviewer
    (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
    • Thread Starter
    Offline

    0
    ReputationRep:
    There are more than 65K records so I can't use excel???
    • Very Important Poster
    • PS Reviewer
    Offline

    21
    ReputationRep:
    Very Important Poster
    PS Reviewer
    (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.
    Offline

    1
    ReputationRep:
    My goodness it's amazing the number of things i just don't understand...
    • Very Important Poster
    • PS Reviewer
    Offline

    21
    ReputationRep:
    Very Important Poster
    PS Reviewer
    (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.
    • Very Important Poster
    • PS Reviewer
    Offline

    21
    ReputationRep:
    Very Important Poster
    PS Reviewer
    (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
    Offline

    1
    ReputationRep:
    Use input mask.
    Offline

    2
    ReputationRep:
    (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
 
 
 
Poll
Black Friday: Yay or Nay?
Useful resources

The Student Room, Get Revising and Marked by Teachers are trading names of The Student Room Group Ltd.

Register Number: 04666380 (England and Wales), VAT No. 806 8067 22 Registered Office: International House, Queens Road, Brighton, BN1 3XE

Write a reply...
Reply
Hide
Reputation gems: You get these gems as you gain rep from other members for making good contributions and giving helpful advice.