Mid Function - Variable length field

[hall.teresa@gmail.com]

Hi everyone -

I have a field where I need to extract the middle portion. The data is
similar to this:

1111-6-652
or
1111-9X4.5-600

What I need is the data between the hypens, either the "6" as in the
first example or the "9X4.5" as in the second example.

I've tried using the Mid and InStr functions but just can't get it to
work.

Any suggestions?

[Scott McDaniel]

Re: Mid Function - Variable length field

On 6 Nov 2006 07:56:23 -0800, "hall.teresa@gm ail.com" <hall.teresa@gm ail.comwrote:

>Hi everyone -
>
>I have a field where I need to extract the middle portion. The data is
>similar to this:
>
>1111-6-652
>or
>1111-9X4.5-600
>
>What I need is the data between the hypens, either the "6" as in the
>first example or the "9X4.5" as in the second example.
>
>I've tried using the Mid and InStr functions but just can't get it to
>work.

You can use this function. Call it like this:

msgbox GetMiddle( "111-4-999")


Function GetMiddle(Strin gToParse As String) As String

Dim intFirst As Integer
Dim intSecond As Integer

intFirst = InStr(1, StringToParse, "-")
intSecond = InStr(intFirst + 1, StringToParse, "-")

GetMiddle = Mid(StringToPar se, intFirst + 1, intSecond - (intFirst + 1))


End Function




Scott McDaniel
scott@takemeout _infotrakker.co m

https://www.infotrakker.com

[Albert D. Kallal]

Re: Mid Function - Variable length field

try:


split("111-9x4.5-600","-")(1)


or even in the query builder:

MyMiddle:split([YourFieldName],"-")(1)


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com