Manipulate data in a field

A

Alan Gordon

How can I strip the first three characters from a value in
a field and return the remaining characters. The values
contained in the fields are not of the same length. I want
to do this using an update query.
 
T

Tom Ellison

Dear Alan:

I recommend the InStr() function, omitting the 3rd parameter:

InStr(YourField, 3)

You may need to test to see if the value is at least 4 characters long
first.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
F

fredg

Dear Alan:

I recommend the InStr() function, omitting the 3rd parameter:

InStr(YourField, 3)

You may need to test to see if the value is at least 4 characters long
first.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Click to expand...

If I understand your question, the field value is
abcdefghi
and you want to update the field to
defghi
Then...

Update YourTable Set YourTable.FieldName = Mid([FieldName],4)
Where YourTable.FieldName Is Not Null And Len([FieldName]) >3;
 
T

Tom Ellison

Or, like Fred says, use the MID function (same thing as InStr, but for
Jet instead of MSDE - I keep forgetting that more of you use Jet than
MSDE).

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Dear Alan:

I recommend the InStr() function, omitting the 3rd parameter:

InStr(YourField, 3)

You may need to test to see if the value is at least 4 characters long
first.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Click to expand...

If I understand your question, the field value is
abcdefghi
and you want to update the field to
defghi
Then...

Update YourTable Set YourTable.FieldName = Mid([FieldName],4)
Where YourTable.FieldName Is Not Null And Len([FieldName]) >3;
Click to expand...
 
Top