Trimming leading & trailing text in a query 2

[bigchuck]

I am looking for the right function(s) to trim a constant number of leading and trailing characters off of a text field of varying length.
Example:
I start with these strings:
123_abcdefg_789
456_zyxwvu_987

I want these results:
abcdefg
zyxwvu

I can get the first four characters off using a mid function; however, it's the trailing characters that are giving me fits.
Any ideas?

 

[mpastore]

This should work:

?mid("123_abcdefg_789",instr("123_abcdefg_789","_&quot+1,instrrev("123_abcdefg_789","_&quot-instr("123_abcdefg_789","_&quot-1)

Or in your query:

select mytext
,mid([mytext],instr([mytext],"_&quot+1,instrrev([mytext],"_&quot-instr([mytext],"_&quot-1) as strip_text
from mytable


Mike Pastore

Hats off to (Roy) Harper

 

[bigchuck]

I think I got it. I wrote the following function

Public Function excise(strField As String, intStartTrim As Integer, intEndtrim As Integer)
'Function to trim a constant amount from the beginning and end from a string
'intStartTrim is the number of characters to be trimmed from the beggining
'intEndTrim is the number of characters to be trimmed from the end

Dim intLength As Integer

'intLength is the total length of the variable length string minus the trimmed lengths
intLength = Len(strField) - intEndtrim - intStartTrim

'Now the function is reduced to using a mid function using the above variables as arguments
excise = Mid(strField, intStartTrim, intLength)
End Function

 

[judgehopkins]

I am wanting to trim leading and trailing spaces, vbProperCase, and remove double spaces in the string, all in a query.

Is that possible?

My question is posted at thread701-584237

Thanks!

"The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?" Anthony Burgess, A Mouthful of Air