Parsing the left and right characters of a string

[stanger1]

I have the following string: NE1220904
I need to remove the left two characters and the right four characters of a string in a query. I have found how to remove one side or the other, but not from both sides. Thanks in advance for the help!

 

[mcarlin]

Try using

x = "NE1220904"
y = Left(x, 5) 'keep 5 left-most chars
z = Right(y, 3) 'keep 3 right-most chars

z should contain "122"

Hope this helps!

 

[stanger1]

How would I enter this into a field for a query.

Thanks!

 

[mresann]

Actually, this operation can be done in one line as follows:

Right(Left("NE1220904", 5),3)

If the length of the string does NOT change from field to field, using the Mid function is easier, as follows:

Mid("NE1220904", 3, 4)

In this scenario, the Mid function uses three arguments. The first argument is the string to be trimmed. The second argument is the character position that the new string starts. In this case, it is right after "NE". The third argument is the number of characters to include in the new string.

In a query design grid, if the name of the field that contains the string is called "Whole", then put this in the top box:

Part: Right(Left([MyTable].[Whole], 5),3)

OR

Part: Mid([MyTable].[Whole], 3, 4)

This will create a field "Part" in the query that provides the string trimming you want.

 

[stanger1]

Thanks for the reply! I tried your suggestion. The middle of the string is variable length. When the field is smaller than the rest, it places a zero in the field. NE10903 has only one character that I need (1) but the result in the query is 10. Listed below is my code that I am using:

PartRight(Left([Mytable].[Whole],4),2))

Thanks again for your help.

 

[FoFa]

How about?
NewValue: mid([MyCol],3,LEN([MyCol])-6)

 

[stanger1]

Perfect! Thanks for your help!!