Truncate the first 2 characters contained in all cells within 1 column of a table in access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jsun1973@hotmail.com - 27 Nov 2006 21:51 GMT

I hope the title is descriptive enough.

My problem is as follows:

I am trying to truncate the first two characters from all cells
contained in a specific row within a table in an Access DB. All
information within this column (from top down) contain information that
looks like: A-1002-G-0061. I am looking to truncate all the "A-"
parts. The first two characters within the cells range from "A-" to
"E-" (i.e. B-1002-G-0061, C-1002-G-0061, etc.)

I looked into the Replace function, but I can't figure out what they
mean by "Expression as string". I am thinking that I need to link this
to my table, but after a couple of hours of trying to figure out how
that is done, I am about crazy...

Any help on this will be appreciated.

Thanks in advance!

Reply to this Message

MBSNewbie - 27 Nov 2006 23:32 GMT

I would add a column, then use an update query
The Update query would update the new column{New} with the Truncated {Old}
value

the sql view would look like:

UPDATE Table1 SET Table1.new = Right([Old],Len([Old])-2);

Table1 = Table
Old = Original Column
New = New Column

If you didn't want to add a column, it will work the same:
UPDATE Table1 SET Table1.Old = Right([Old],Len([Old])-2);

>I hope the title is descriptive enough.
>
[quoted text clipped - 15 lines]
>
> Thanks in advance!

Reply to this Message

Tom Lake - 28 Nov 2006 01:16 GMT

>I would add a column, then use an update query
> The Update query would update the new column{New} with the Truncated {Old}
[quoted text clipped - 3 lines]
>
> UPDATE Table1 SET Table1.new = Right([Old],Len([Old])-2);

or perhaps faster:

UPDATE Table1 SET Table1.new = Mid([Old], 3);

Tom Lake

Reply to this Message

jsun1973@hotmail.com - 28 Nov 2006 13:44 GMT

> I would add a column, then use an update query
> The Update query would update the new column{New} with the Truncated {Old}
[quoted text clipped - 10 lines]
> If you didn't want to add a column, it will work the same:
> UPDATE Table1 SET Table1.Old = Right([Old],Len([Old])-2);

Your second suggestion worked perfectly. Thanks so much for bringing
me back from the edge of insanity! :D

Reply to this Message