Capture substring to the left of a semicolon. 2

[jasonmac]

Hi everyone. I have a column that contains data resembling the following:

123;455
1000;2000
12.5555;5.5555

I need to be able to capture everything to the left of the semicolon. I am using access 97 with an ODBC connection to a Progress database. Believe me this was not my weapon of choice but I have to work with what I have. I have a query that successfully uses a substring finction but since the semicolon is in a different position every time I need to make the length of what I select dynamic. Can someone please help?

Thanks in advance,
Jason

 

[jebry]

Hi Jason!

Just in case you don't see this in the other post.

Use Left(YourString, InStr(YourString, ";&quot - 1)

hth


Jeff Bridgham
bridgham@purdue.edu

 

[CajunCenturion]

It might be a good idea to insure that the ; exists before using it in the function.

IIf((InStr(YourString, ";") > 0), Left(YourString, InStr(YourString, ";") - 1), YourString)

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[jasonmac]

Thanks to you both. This is to be used in an SQL statement and for some reason the Progress database will not accept my syntax. I'm sure I'm on the right track now.

Thanks Again,
Jason

 

[jasonmac]

Actually that worked perfectly!!!! I just had a typo in the code. Thanks a TON!

 

[jasonmac]

Hi again. A new situation has come up. Live before I have a column that has data like this:

123;455;1000;509
1000;2000;500;610

I need the third set of digits. I need to grab everything between the second and third semicolon(from the left).

Any ideas??

Thanks again,
Jason

 

[CajunCenturion]

If you're using VBA version 6, then the split function is a perfect fit. Otherwise, you might want to consider writing your own split function, or looping thru using the InStr function with the optional parameter of the starting search position.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[jasonmac]

I'm suing a pass-through query in access '97. Not by choice mind you but it is what it is. Is it possible to do what you suggested? If so, I've never written a loop in SQL. What's the syntax?

Thanks again,
Jason

 

[CajunCenturion]

In A97 you don't have the Split function so one approach is to create a Public function in a module, passing in the fieldname as a parameter, preform a looping function using the InStr function and its starting position, and once you have the value, have the function return the string.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[jasonmac]

I think this should be pretty close but I'm still getting an error that says "Undefined function 'StrSplit' inexpression"

Can you see what I've done wrong?

SELECT StrSplit(Test.[Unit-ratio])
FROM Test;

here's the function:

Public Function SplitStr(strUnitRatio As String)
    'this function will extract the digits between the
    'second and third semicolon
    Dim intCounter As Integer
    Dim intPosition1 As Integer
    Dim intPosition2 As Integer
    Dim StrAnswer As String
    
    intCounter = 1
    intPosition1 = 0
    intPosition2 = 0
    Do Until intCounter > 3
        intPosition1 = InStr(intPosition1, strUnitRatio, ";")
        If intCounter = 3 Then
            StrAnswer = Mid(strUnitRatio, intPosition1, intPosition1 - intPosition2)
        End If
        intPosition2 = intPosition1
        intCounter = intCounter + 1
    Loop
    
    strUnitRatio = StrAnswer
        
End Function

I think the function should work but my query doesn't seem to recognize it.

Thanks in Advance,
Jason

 

[NorthNone]

Your code calls StrSplit
You2 function is SplitStr
I think resolving the name will get you back on track.
HTH
JeanS

 

[jasonmac]

Thanks North. That got the query to run. Unfortunately The function isn't returning anything. Now I just need to go through my function and try to find out what's going wrong.

Thanks,
Jason

 

[NorthNone]

How about:
SplitScr = StrAnswer
HTH
NorthNone

 

[jasonmac]

I see what you mean. I've corrected that part but it's still returning nothing. It must have something to do with my loop.

thanks again,
Jason

 

[jasonmac]

In case anyone was wondering, this is what finally worked for me.

Public Function SplitStr(strUnitRatio As String)
    'this function will extract the digits between the
    'second and third semicolon
    Dim intCounter As Integer
    Dim intStartingPoint As Integer
    Dim intPosition1 As Integer
    Dim intPosition2 As Integer
    Dim StrAnswer As String
    
    intCounter = 1
    intStartingPoint = 1
    intPosition1 = 1
    intPosition2 = 0
    
    Do Until intCounter = 4
        
        intPosition1 = InStr(intStartingPoint + 1, strUnitRatio, ";")
        
        If intCounter = 3 Then
            StrAnswer = Mid(strUnitRatio, intStartingPoint + 1, intPosition1 - intPosition2 - 1)
        End If
        intPosition2 = intPosition1
        intStartingPoint = intPosition1
        intCounter = intCounter + 1
    Loop
    
    SplitStr = StrAnswer
        
End Function

 

[NorthNone]

Thanks for the post, Jason. I appreciate knowing how to make it work.
NorthNone