Delete first character off a string

[GregB]

I would like to write a query that deletes the first letter of a string
(serial number field) if it is starts with an S

I have done this with vba realted to a control on a form but can't get it to
work in a query.
Basically I have like 700 records in which I need to delete the S of of the
field [Serial Number]

Please assists
Thank you

 

[Douglas J. Steele]

UPDATE MyTable
SET MyField = Mid([MyField], 2)
WHERE Left([MyField], 1) = "S"

 

[Wayne-I-M]

Hi Greg

Try this

Create a new field in your table (just to test you are happy with the
results) call it
NewFieldName

UPDATE TableName SET TableName.NewField = Mid(TableName!FieldName,2)
WHERE (((TableName.FieldName) Like "S*"));

In the above change FieldName to the name of the field that contains your
data that you want to remove the S's
Change TableName to what it really is

 

[Jeff Boyce]

Greg

Sometimes folks say they want to "delete" something when what they really
mean is that they want to display something without something else (e.g., I
want to display my serial number without the leading "s").

Which definition are you asking about?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[GregB]

No I defintley need to delete the S's but vbery good point Jeff.
Works great, thanks Douglas and Wayne (*Did Waynes way)

Greg

Sometimes folks say they want to "delete" something when what they really
mean is that they want to display something without something else (e.g., I
want to display my serial number without the leading "s").

Which definition are you asking about?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I would like to write a query that deletes the first letter of a string
(serial number field) if it is starts with an S

I have done this with vba realted to a control on a form but can't get it
to
work in a query.
Basically I have like 700 records in which I need to delete the S of of
the
field [Serial Number]

Please assists
Thank you