Adding a prefix to a column

[bolts]

I have no experience with Access, but I would like to take an existing
table and add an "E" to the beginning of every number in the "Item
Number" field of a table with about 5,000 records.
Needless to say...I'd rather not do it manually. I've looked around in
all of the Access resources that I can find (Access 2000, by the way),
and just can't seem to find an answer.
Any suggestions on how to accomplish this?

 

[Allen Browne]

Do you need the E prefix because there will, in the future, be different
prefixes as well? If not, you can just solve the problem by printing the E
where ever you need, and not worrying about changing the data. For example,
you could put this expression into the Control Source of a text box on your
report:
="E" & [ItemNumber]
Be sure to change the Name property as well: Access gets confused if the
text box has the same Name as a field, but is bound to something else.

If you do need to insert the prefix, check the data type first.
Open the table in design view.
Is it a Number type, or a Text type?
If it is Number, must change it to Text before it can accept the E.
If you change it to Text, it will sort differently - character by character.
That means 10 sorts before 9 (because the first character, the 1, is less
than 9.)

To actually change the field:
1. Create a query using this table.

2. Change it to an Update query (Update on Query menu.)
Access adds an Update row to the grid.

3. Drag the [Item Number] field into the grid.

4 In the Update row, enter:
"E" & [Item Number]

5. Run the query.

 

[bolts]

I'm guessing that your last solution is the one that I will need.
What I am trying to do is add the data from this Access 2000 table to
a SQL 2000 server. The server is running an ERP called Made2Manage,
which uses Visual FoxPro database containers. Anyway, I need to merge
that data into the existing database, but some of the "Item Numbers"
in the Access table may conflict with "Item Numbers" that already
exist in the FoxPro tables, so we are going to test to see if adding
an "E" to the beginning of each of the "Item Numbers" allows us to add
them as well as giving us a quick way to identify these parts that are
used for older equipment.
Does it sound to you, like we are heading in the right direction?
And by the way...thanks for the help.

 

[Allen Browne]

That should work, with the provisos in the original answer (e.g. that it is
a text field.)