I need to add a charachter to mulitple records in a field

[AndrettiAWG]

I'm working with MS Access 2003. I have a datasheet that has to have the
letter A added to every record within the Order Number field. Does anyone
know how I can do this easily? I don't want to, nor do I have the time, to
go into each individual line to add the letter. HELP!

 

[John Spencer]

Use an update query.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

UPDATE [Your Table]
SET [Your Field] = "A" & [Your Field]
WHERE [Your Field] Not Like "A*"

In query design view
-- New Query
-- Add your table
-- Add your field to the field grid
-- ENTER the following into the criteria
Not Like "A*"
-- Select Query: Update from the menu
-- Enter the following in the UPDATE To block
"A" & [Your Field]

If you want the "A" to be added to the end then you need criteria of
Not Like "*A"
And UPDate To to read
[Your Field] & "A"


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Dirk Goldgar]

I'm working with MS Access 2003. I have a datasheet that has to have the
letter A added to every record within the Order Number field. Does anyone
know how I can do this easily? I don't want to, nor do I have the time,
to
go into each individual line to add the letter. HELP!

Create and run an update query that sets the [Order Number] field to ("A" &
[Order Number]). SQL would look something like this:

UPDATE YourTableName
SET [Order Number] = "A" & [Order Number]
WHERE [Order Number] Is Not Null;

Note, though, that totally invariant data, such as a leading "A", doesn't
really have to be stored in the database. You can always use and expression
or a format to display the leading A. That does involve a bit more work,
though, than just updating the table to tack on the "A".

 

[AndrettiAWG]

Hi Dirk -

Thanks for the help! It is greatly appreciated!

Andretti Winery
Napa, CA

I'm working with MS Access 2003. I have a datasheet that has to have the
letter A added to every record within the Order Number field. Does anyone
know how I can do this easily? I don't want to, nor do I have the time,
to
go into each individual line to add the letter. HELP!

Create and run an update query that sets the [Order Number] field to ("A" &
[Order Number]). SQL would look something like this:

UPDATE YourTableName
SET [Order Number] = "A" & [Order Number]
WHERE [Order Number] Is Not Null;

Note, though, that totally invariant data, such as a leading "A", doesn't
really have to be stored in the database. You can always use and expression
or a format to display the leading A. That does involve a bit more work,
though, than just updating the table to tack on the "A".

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[AndrettiAWG]

Good Afternoon John -

Thanks for the help! It's appreciated!

Andretti Winery
Napa, CA

Use an update query.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

UPDATE [Your Table]
SET [Your Field] = "A" & [Your Field]
WHERE [Your Field] Not Like "A*"

In query design view
-- New Query
-- Add your table
-- Add your field to the field grid
-- ENTER the following into the criteria
Not Like "A*"
-- Select Query: Update from the menu
-- Enter the following in the UPDATE To block
"A" & [Your Field]

If you want the "A" to be added to the end then you need criteria of
Not Like "*A"
And UPDate To to read
[Your Field] & "A"


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I'm working with MS Access 2003. I have a datasheet that has to have the
letter A added to every record within the Order Number field. Does anyone
know how I can do this easily? I don't want to, nor do I have the time, to
go into each individual line to add the letter. HELP!