Select Part of field

S

SR

I have a field that for EVERY record has a unique text/number
followed by a common suffix so 2 records would look like this:

sfxhdbyrneo1234
hey7dtdbfkri503mf812n1234

How do I create a query that would copy the entire field EXCEPT the
common suffix?

I can also run this as an update query to just delete the common
suffix.

Thanks
 
A

Andy Hull

Hi

Firstly, make sure you back up your table.

Then, it will depend on the data type of the field you want to change, but,
using the sample data you gave, ie, a text field where you want to omit the
last 4 characters...

update tblMyTable
set MyField = left(MyField, len(MyField) - 4)

hth

Andy Hull
 
J

John Spencer

Being a tad paranoid, I would add a where clause to the update query

UPDATE yourTable
SET YourField = Left(YourField,Len(YourField)=4
WHERE YourField Like "*1234"

This would remove 1234 from records that ended in 1234, but would not
truncate records that ended in 1111.

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