Re-Formatting Existing Data

  • Thread starter Thread starter SITCFanTN
  • Start date Start date
S

SITCFanTN

I need to import an Excel Spreadsheet into my table, the spreadsheet has
4,000 rows. The text format of the names is column C is not correct, some
names are all caps, some are all lower case, some are first letter cap....I
want to clean up this file and make all names First Name Caps only, is there
a quick and easy way to do this with code.. Thank you.
 
You could do this with an Update query, setting the [FirstName] field to
Proper([FirstName).
 
UPDATE YourTable
SET [FirstNameField] = UCase([FirstNameField])
WHERE [FirstNameField] is not null

In Design view
-- Select the table
-- Add the First name field to the grid
-- Set the criteria to Is Not Null
-- Select query: Update from the menu
-- enter the following in the Update to
UCase([FirstNameField])
-- Select Query:Run from the menu

If you want to upper case the intial letters of each word you can use this
expression in the Update To "cell"
StrConv([FirstNameField],3)

BACK UP your data before you try this. Once it works to your satisfaction,
you can stop backing up your data.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Ed Robichaud said:
You could do this with an Update query, setting the [FirstName] field to
Proper([FirstName).


SITCFanTN said:
I need to import an Excel Spreadsheet into my table, the spreadsheet has
4,000 rows. The text format of the names is column C is not correct,
some
names are all caps, some are all lower case, some are first letter
cap....I
want to clean up this file and make all names First Name Caps only, is
there
a quick and easy way to do this with code.. Thank you.
Click to expand...
Click to expand...
 
Back
Top