Select Part of field

  • Thread starter Thread starter SR
  • Start date Start date
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
 
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
 
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
..
 
Back
Top