Manipulate data in a field

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