Get Data left of a character in a string value

[sibleytr]

I want to obtain all data from a string that is to the left of a givem character.

The data string is a network path: "2005-A\AAA00001"

I want to get all data to the left of the "\" character. I can use the Left(text,6) command but the folder name has the possiblity of changing character length.

I have been unable to find anything for an Access Query, so I'm assuming I need to write a function.

Any ideas?

 

[FancyPrairie]

Call a function that looks like this:

Function GetText(strText as string) as string
Dim i as integer

i = instr(1,strText,"\")

if (i > 1) then
GetText = Left$(strText,i-1)
Else
GetText = vbNullString 'or Whatever if no "\"
End if

End Function

 

[sfreeman]

Or,

Public Function GetPath(strInput) As String
Dim intSub As Long
Dim strArr() As String

strArr = Split(strInput, "\")
GetPath = strArr(0)

End Function

Sam_F
"90% of the problem is asking the right question.

 

[JeremyNYC]

Not that there's anything wrong with the above methods, just that it seemed fun to chime in...

function fStrChop(strOld as string, strDelimiter as string) as string

fStrChop = left(strold, instr(strnew, strDelimiter) - 1)

end function

Jeremy
NB: the above is untested aircode, though I've written it enough times in the past that I'm relatively comfortable with it.

---
Jeremy Wallace
METRIX Project Coordinator
Fund for the City of New York

 

[FancyPrairie]

Any one of these will work as long as the string contains "\". If it doesn't, then sFreeman's method returns the whole string and JeremyNYC returns an error.

 

[sfreeman]

[corrected]

Or,

Public Function GetPath(strInput) As String
Dim strArr() As String
strArr = Split(strInput, "\")
GetPath = strArr(0)
End Function

Sam_F
"90% of the problem is asking the right question.

 

[Blorf]

Hi. I don't think a function is required here.

How about left([Field], instr ([Field], "/")-1)

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://mysite.verizon.net/res0n4sv/

 

[FancyPrairie]

Blorf, that will work as long as the string contains "\". Else you will receive an error. Because Instr will return -1, which would not be valid.

 

[Blorf]

Good point. He refered to a path, so probably not a prolem. But below is fixed so no matter what it won't error.

Thanks,

ChaZ

left([Field], iif (instr ([Field], "/") = 0, len ([field]), instr ([Field], "/")-1))


There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://mysite.verizon.net/res0n4sv/

 

[FancyPrairie]

Another minor FYI...

Left$ is faster than Left if returning string.

If then else is 30% faster than IIF

 

[JeremyNYC]

Well OK then!

function fStrChop(strOld as string, strDelimiter as string) as string
dim intLoc as integer

intloc = instr(strnew, strDelimiter) 
if intloc > 0 then
  fStrChop = left(strold, intLoc - 1)
else
  fstrchop = strold
end if

end function

---
Jeremy Wallace
METRIX Project Coordinator
Fund for the City of New York

 

[sibleytr]

So far the winner appears to be the function

Public Function GetPathLeft(strInput) As String
 Dim intSub As Long
 Dim strArr() As String
  strArr = Split(strInput, "\")
  GetPathLeft = strArr(0)
End Function

Public Function GetPathRight(strInput) As String
 Dim intSub As Long
 Dim strArr() As String
  strArr = Split(strInput, "\")
  GetPathRight = strArr(1)
End Function

Where as

([Field], instr ([Field], "/")-1)

errors out

left([Field], iif (instr ([Field], "/") = 0, len ([field]), instr ([Field], "/")-1))

Gives the full path (what I started with), but I'm still hacking on it.

Thanks to everyone for helping out!!!

 

[sfreeman]

So.... what do I win???

:-B

Sam_F
"90% of the problem is asking the right question.

 

[Blorf]

Hi. My formula has a fatal flaw :-(

I used forward slash "/" instead of back slash "\"

Fix that, and I think it should work for you.

Maybe I can come in a distant second to Sam_F?

Thanks,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://mysite.verizon.net/res0n4sv/

 

[JeremyNYC]

TankHumper,

I'm thinking sfreeman should at least win a star...just click on the link to thank him (I'm guessing, though Sam could be Samantha) for the valuable post.

Jeremy

---
Jeremy Wallace
METRIX Project Coordinator
Fund for the City of New York

 

[sibleytr]

Your right it is a type-o "/" instead of "\". Sorry. When you look a something long enough it reads differently then what it is. The code works great in the Access Query. Thanks again.

 

[Blorf]

Glad to be of service.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://mysite.verizon.net/res0n4sv/