variable length trim of unwanted chars

[shannonphillips]

I have [field1] with a variable length name where I need to remove the fixed proceeding and trailing chars.
example
ACC1201HOA
ACC415HOA
ACC1851HOA

so end up with just

1201
415
1851

I'm right clicking on the Update to: of my Update query, chosing Build and trying to use some previous RTrim, LTrim examples, and getting no where accept completely overwritting my values..

Thanks

 

[PWise]

How about
val(mid(field1,4))

 

[PHV]

To remove 3 leading and 3 trailing chars:
Mid(Left(field1,Len(field1)-3),4)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shannonphillips]

pwise option leaves me with 0
phv Access converts to Mid(Left("field1",Len("field1")-3),4) and leave me with blank fields

 

[PHV]

In my post, replace field1 with the real name of you field ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shannonphillips]

Replacing the " "with [ ] worked

Mid(Left([field1],Len[field1])-3),4)

many thanks!