Re-Formatting Existing Data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SITCFanTN - 04 Dec 2007 13:41 GMT

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.

Reply to this Message

Ed Robichaud - 04 Dec 2007 13:58 GMT

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

>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
[quoted text clipped - 3 lines]
> there
> a quick and easy way to do this with code.. Thank you.

Reply to this Message

John Spencer - 04 Dec 2007 14:19 GMT

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.

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> You could do this with an Update query, setting the [FirstName] field to
> Proper([FirstName).
[quoted text clipped - 7 lines]
>> there
>> a quick and easy way to do this with code.. Thank you.

Reply to this Message