Puzzling code -

[sfreeman@co.mer]

In trying to assist another member, I created a simple setup.

tblValues has one field named 'Value', which holds 3 random text records, each containing one or more strings separated by a comma and a space.

Value
434534
3454534, 7888, 44fxcew, eqeew
ewrqw, 5643, art4345, 32af

The Function 'ParseText' is intended to accept each record via a simple query...

"SELECT
ParseText([Value]) AS Val1
FROM
tblValues;"

and place each value into a new table 'tblValuesNew' [identical to first table].

/************************
Public Function ParseText(strSearchString As String)
Dim db As DAO.Database
Dim rst As DAO.Recordset

Dim strSQL As String
Dim strWord As String
Dim strChar As String
Dim i As Integer
Dim intLen As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("tblValuesNew", dbOpenTable)

strWord = ""

intLen = Len(strSearchString)
For i = 1 To intLen + 1
strChar = Mid(strSearchString, i, 1)
If strChar <> " " And strChar <> "," And strChar <> "" Then
strWord = strWord & strChar
ElseIf strWord <> "" Then
With rst
.AddNew
!Value = strWord
.Update
strWord = ""
End With
End If
Next i

strSearchString = ""

Set db = Nothing
Set rst = Nothing

End Function
/************************

The expected recordset should contain 9 records:
434534
3454534
7888
44fxcew
eqeew
ewrqw
5643
art4345
32af

For some reason, the query seems to process the first record twice thereby inserting the values, one or several, twice.

Anyone?

 

[Mile-O]

Have a look at this thread.

Any help?

 

[sfreeman@co.mer]

Thank you, I will look at that to see if it will help, but I should have made clear that we will not always be assured that the delimiter will be a comma.

 

[Mile-O]

Thank you, I will look at that to see if it will help, but I should have made clear that we will not always be assured that the delimiter will be a comma.

If you loop through your string you can send the delimiter.

 

[sfreeman@co.mer]

Here is my simple table [tblValues].

Value
Dave
Pete, Ron, Sam
John, Ralph, Susan, Robin

My query:

SELECT
GetPart([Value],",",1) AS Expr1

FROM
tblValues;

Results in an error message #5 'Invalid procedure call or argument.' [this comes up twice in processing this query]

Resulting data 3 records [no value for 1st record and note spaces preceding values]:

Expr1

Ron
Ralph

Have I not set this up correctly?

 

[john471]

I assume you have obtained GetPart from the thread linked to by SJ McAbney ?

You don't specify which version of the GetPart function you are using. I don't have the luxury of using the 2000+ version.

If you are using the first version, and go to a debug/immediate window and enter
?GetPart("Dave",",",1)
(which is effectiveley what the first row of your query is doing)
You will find that you get an error.
This is because you are asking for the second field of a one field string (As SJ McAbney states, this is a zero based function arguement), and this function does not cater for this condition.
?GetPart("Dave",",",0)
however, would return "Dave".

I offer some code I wrote to meet a similar need. It does cater for the error condition you are creating:-
' Function: szNthFieldStringParser
' Description: Returns the Nth field in a delimited string
' If the string has less than N fields, a zero length string is returned
' If N is less than 1, a zero length string is returned
' If the Delimiter is a zero length string, the input string is returned
' Consecutive delimiters can be treated as one - pass

You would use it as follows
SELECT
szNthFieldStringParser([Value],1,false,",") AS Expr1
FROM
tblValues;

OR

SELECT
szNthFieldStringParser([Value],1) AS Expr1
FROM
tblValues;
(the default values for the optional parameters are false and comma (",").)


NB: My function's nField parameter is one based, not zero based.

'**************************************************************************************************************
'   Function:    szNthFieldStringParser
'
'   Version Hiostory:
'                V1.0 Original Version
'                V1.1 Szinput now ByVal, trailing and leading delimiters only added if not already there.
'
'   Description: Returns the Nth field in a delimited string
'                If the string has less than N fields, a zero length string is returned
'                If N is less than 1, a zero length string is returned
'                If the Delimiter is a zero length string, the input string is returned
'                Consecutive delimiters can be treated as one - pass bTreatConsecutiveDelimitersAsOne = true
'
'   Parameters:
'               szinput - the delimited string to return the Nth Field of
'               nField - The number of the field to return
'
'   Optional Parameters:
'               bTreatConsecutiveDelimitersAsOne - True to treat consecutive delimiters as one
'               szDelimiter - the delimiter to use (can be multi-character e.g "::")
'
'**************************************************************************************************************

Public Function szNthFieldStringParser(ByVal szInput As String, nField As Integer, Optional bTreatConsecutiveDelimitersAsOne As Boolean = False, Optional szDelimiter As String = ",") As String
    Dim nPosStart As Integer
    Dim nPosEnd As Integer
    Dim nCounterField As Integer
    Dim nLenDelimiter As Integer
        
    nPosEnd = 1
    nPosStart = 1
    nLenDelimiter = Len(szDelimiter)
    
    nCounterField = 0
    
    'Handle the exceptions
    If nField < 1 Then
        szNthFieldStringParser = ""
        Exit Function
    End If
    
    If szDelimiter = "" Then
        szNthFieldStringParser = szInput
        Exit Function
    End If
    
    'Ensure there is a leading and trailing delimiter, for ease of processing
    'szInput = szDelimiter & szInput & szDelimiter
    If Not Left$(szInput, nLenDelimiter) = szDelimiter Then szInput = szDelimiter & szInput
    If Not Right$(szInput, nLenDelimiter) = szDelimiter Then szInput = szInput & szDelimiter

    
    'Find the Start and End positions of the Nth field
    Do
        
        nCounterField = nCounterField + 1
        
        'Handle consecutive delimiters if they are to be treated as one
        If bTreatConsecutiveDelimitersAsOne And Mid(szInput, nPosEnd + nLenDelimiter, nLenDelimiter) = szDelimiter Then
            nCounterField = nCounterField - 1
        End If
        
        nPosStart = InStr(nPosEnd, szInput, szDelimiter, vbTextCompare)
        nPosEnd = InStr(nPosStart + nLenDelimiter, szInput, szDelimiter, vbTextCompare)
    
    'nPosEnd will = 0 if there aren't enough delimiters in the input string for N fields
    Loop While nCounterField < nField And nPosEnd > 1
    
    If nPosEnd > 1 Then
        szNthFieldStringParser = Mid(szInput, nPosStart + nLenDelimiter, nPosEnd - nPosStart - nLenDelimiter)
    Else
        szNthFieldStringParser = ""
    End If


End Function

HTH

Regards

John.

 

[WayneRyan]

John,

Well done!

Thanks (nice example),
Wayne