Solution to delete 1st character in a column of data

  • Thread starter Thread starter AndrewB
  • Start date Start date
A

AndrewB

Without having to export my data out of Access and do a find/replace in Excel
then re-insert back into Access... How can I query "Column1", which has data
exactly like "C1234567", remove the letter in front of the 7 numbers, and
keep the 7 numbers?

Thanks :-)
 
Use an update query

UPDATE YourTable
SET YourField = Mid([YourField],2)
WHERE Len(YourField)= 8

Or to be even safer use the following which will only strip off the leading
character if the first character is a letter followed by 7 numbers.

UPDATE YourTable
SET YourField = Mid([YourField],2)
WHERE YourField Like "[a-z]#######"


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
John !!! Yu DA Man!

Thank You, Thank You, Thank You!!!! This works PERFECTLY!

Thanks so much,
--
AndrewB.


John Spencer said:
Use an update query

UPDATE YourTable
SET YourField = Mid([YourField],2)
WHERE Len(YourField)= 8

Or to be even safer use the following which will only strip off the leading
character if the first character is a letter followed by 7 numbers.

UPDATE YourTable
SET YourField = Mid([YourField],2)
WHERE YourField Like "[a-z]#######"


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

AndrewB said:
Without having to export my data out of Access and do a find/replace in
Excel
then re-insert back into Access... How can I query "Column1", which has
data
exactly like "C1234567", remove the letter in front of the 7 numbers, and
keep the 7 numbers?

Thanks :-)
Click to expand...
Click to expand...
 
AndrewB said:
Without having to export my data out of Access and do a find/replace in Excel
then re-insert back into Access... How can I query "Column1", which has data
exactly like "C1234567", remove the letter in front of the 7 numbers, and
keep the 7 numbers?
Click to expand...


Are you sure you want to totally discard the first
character?

Is every record guaranteed to have a letter in the first
character.

If you might want to keep the fiest character in the table,
then just retrieve the other characters whenever you need to
leave it out. In this case, you can use a claculated field:

SELECT column2, column3, . . .
IIf(Column1 Like "[!0-9]*", Mid(Column1, 2), Column1)
As abc
FROM table

If you really want to change the field in the table (instead
of just not retrieving it in a SELECT query), then you can
use an UPDATE query:

UPDATE table
SET Column1 = Mid(Column1, 2)
WHERE Column1 Like "[!0-9]*"
Be sure to back up the table before running a query that can
mess over a lot of records.
 
Back
Top