Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2004
    Posts
    113

    Unanswered: help using mid fucntion

    hey guys,
    how do i query to pull out everything in a string of characters until it hits the 2nd space " ". example The Walt Disney Company would result to "The Walt"

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    frankly, I dont know any sql construct to this job. unless a SQL wiz suggest another option, my advice is create public function in VBA that extracts first 2 words and use that function in your query for the field.
    ghozy.

  3. #3
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    Here is a hint: Use the InStr function which returns the position of N occurance of a character. Then use that result with the Left function.

    Booyaa!

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    InStr returns the FIRST occurance of a given character. It does not have a parameter to specify N occurance.

    InStr([start, ]string1, string2[, compare])
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    ok here is the code for extracting N words from a field.
    Code:
     
    Public Function ExtractWords(Afield As Variant, NumofWords As Integer)
    Dim Counter, LastPos As Integer
    ' Usage: ExtractWords( FieldName, NumberOfwordsToExtract)
     
    ExtractWords = Null
    If Not IsNull(Afield) Then
    	Afield = Trim(Afield)
    	For Counter = 1 To NumofWords
    		LastPos = InStr(LastPos + 1, Afield, " ")
    		If LastPos = 0 Then
    			ExtractWords = Afield
    			Exit Function
    		End If
    	Next
    	ExtractWords = Left(Afield, LastPos - 1)
    End If
     
    End Function
    and an example how to use it;
    SELECT ExtractWords([fieldnamehere],2) AS ShortName FROM Table1;
    Last edited by ghozy; 09-22-04 at 14:25. Reason: Typo
    ghozy.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I went a little different route. I basically extended the InStr function to allow you to specify the N'th occurance of a given string within another given string. You could use this in conjunction with Left() to return your string up to the Nth occurance of a given character, in this case " ".

    Code:
    Public Function FindOccurance(str1 As String, str2 As String, occ As Integer) As Integer
    Dim s As String
    Dim i As Integer
    Dim x As Integer
    Dim L As Integer
    
    
    s = str1
    x = InStr(str1, str2)
    L = x
    
    For i = 2 To occ
        s = Right(s, Len(s) - x)
        x = InStr(s, str2)
        L = L + x
    Next i
    
    If x = 0 Or IsNull(x) Then
        FindOccurance = 0
    Else
        FindOccurance = L
    End If
    
    End Function
    Usage example:

    SELECT Left(YourField, FindOccurance(YourField, " ", 2))

    My test string was:

    one two three

    FindOccurance(test, " ", 3) = 8
    Left(txtTest, FindOccurance(txtTest, " ", 2)) = one two
    Last edited by Teddy; 09-22-04 at 15:12.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    Of course you could do it with built in functions like:


    Trim(Left([FieldNameHere], InStr(InStr(1, [FieldNameHere], " ") + 1, [FieldNameHere], " ")))


    ex:

    SELECT Trim(Left([FieldNameHere], InStr(InStr(1, [FieldNameHere], " ") + 1, [FieldNameHere], " "))) AS ShortName FROM Tabe1;

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    This is a function I wrote that has been very useful. It is based on a function I used to use in the MUMPS programming language. It is called Piece. You pass it a string, a delimiter, the first piece you want through the last piece you want. The delimiter can be one or more characters and it returns and empty string if there is nothing to return.

    You would call it like this:

    Piece("The Walt Disney Company"," ",1,2)

    Here is the code:

    Code:
    Public Function Piece(varString As Variant, _
                          strDelimiter As String, _
                          lngBegin As Long, _
                          Optional varEnd As Variant) As String
                          
    'Description :  This function will return a piece of a delimited string.  The delimiter can be one or more
    '               characters, and it can return one or more pieces of the string.  Ex 4th through the 6th piece.
    '
    'Parameters :   strString               The delimited string
    '               strDelimiter            The delimiter, can be one or more characters
    '               lngBegin                The beginning piece of the string to return
    '               varEnd                  Optional parameter, if given return a range of pieces, starting from intBegin
    '                                       to intEnd
    '
    'Return :       String. The string between the intBegin occurence of the delimiter to the intEnd occurence of the
    '               delimiter.
    
    '11/03/03 DCK   Original function.
                          
                          
        Dim lngEnd As Long
        Dim lngBeginPos As Long
        Dim lngEndPos As Long
        Dim lngCounter As Long
        Dim lngDelimiter As Long
        Dim intDelimiterLen As Integer
        Dim booEnd As Boolean
        Dim booAddEndAdjustment As Boolean
        Dim strString As String
        
        'If the string being passed in is an empty string then exit
        If Nz(varString, "") = "" Then
            Piece = ""
            Exit Function
        Else
            strString = CStr(varString)
        End If
        
        lngCounter = lngBegin - 1
        lngBeginPos = 1
        booEnd = False
        
        intDelimiterLen = Len(strDelimiter)
        
        'Skip anything before the delimiter requested
        For lngDelimiter = 1 To lngCounter
            lngBeginPos = InStr(lngBeginPos, strString, strDelimiter)
            
            'Correct for the case where there are no more delimiters in the string
            If lngBeginPos = 0 Then
                lngBeginPos = Len(strString)
                booAddEndAdjustment = False
                
                'If there are no more delimiters then stop looking
                lngDelimiter = lngCounter
            Else
                lngBeginPos = lngBeginPos + intDelimiterLen
                booAddEndAdjustment = True
            End If
        Next lngDelimiter
        
        'If the user did not enter an ending delimiter then set it
        If IsMissing(varEnd) Then
            varEnd = lngBegin
        End If
        
        lngEnd = CLng(varEnd)
        lngEndPos = lngBeginPos
        For lngDelimiter = lngBegin To lngEnd
            lngEndPos = InStr(lngEndPos, strString, strDelimiter)
            
            'Correct for the case when there are no more delimiters in the string
            If lngEndPos = 0 Then
                lngEndPos = Len(strString)
                booEnd = True
                
                'If there are no more delimiters then stop looking
                lngDelimiter = lngEnd
            Else
                lngEndPos = lngEndPos + intDelimiterLen
            End If
        Next lngDelimiter
        
        If booEnd Then
            If lngBeginPos <> lngEndPos Or booAddEndAdjustment Then
                'Adjust the ending position so that the subtraction works in the final line (Mid)
                lngEndPos = lngEndPos + 1
            End If
        Else
            'If there are still delimiters in the string then remove the last delimiter
            lngEndPos = lngEndPos - intDelimiterLen
        End If
        
        Piece = Mid(strString, lngBeginPos, lngEndPos - lngBeginPos)
        
    End Function

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's prety rad. I would think you could rewrite that using split and an array though, no?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I am sure it could be written several different ways. But this worked for me and it is fast enough for what I need it for.

    I use it quite a bit when I pass in OpenArgs to forms. I put parameters in a delimited list and then in the form split it up. Once you start using it, you will find several uses for it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •