How can I pull a list string apart?

[debizabor]

I have a list string (i.e. bio, eng, med, phys) How can I ull them apart and run a count on each of them?

Help is greatly appreciated.

 

[Golom]

Need a bit more info ...

Do you have these in a field in a table or in a variable in your program?

Where are they that you want to "... run a count ..."?

 

[debizabor]

They are in a field.

Interest area: bio, chem, cell, lab

I weant to find out everyone that has bio in their interest area and count them.

 

[SgtJarrow]

what you want is the following....in you query add a field that has:

InStr(1, [fieldname], "bio")

in the field line. This will return a value. Any value greater than 0 means the word bio appears in the field. Then just filter based on bio and sum it up.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[debizabor]

What if I want everything in the string pulled apart? Do I have to do them all separate?

 

[SgtJarrow]

What exactly are you trying to do???

Do you wish to go through all the records, read this field, and make a count of all the different topics listed? Such as:

John Doe bio, chem, cell, lab
Jane Smith bio, cell, work
Kerry Jones cell, work

results:
bio 2
chem 1
cell 3
lab 1
work 2

Does that sound like the desired?? If so, I would recommned VBA coding for this. Create a recordset of the records in question. Give yourself a multi-dimensional array to work with. Loop through the records, and read the interest field. Loop through the fields, using commas as your separators, and fill the array as necesary. Return the array back wherever you want it.

This can get kind of complicated, but not too difficult depending on where you want the results to go and all that. Please give usd a bit more to work with and we'll help you solve your problem.


=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[debizabor]

That is exactly what i want to do, but do not know how to do VBA coding. There aren't any functions I can set up for this?

 

[Golom]

What I usually tell people who want to do that is "don't go there" ... however, if you must ...

Set up a table of integers with one field called "num" and load the values from 1 to the maximum length of your longest string that you want to parse + 2. Then you can run
[blue][tt]
SELECT Mid$("," & I1.instring & ",", S1.num + 1 , S2.num - S1.num - 1) AS [SubString]

FROM InputStrings AS I1, integers AS S1, integers AS S2

WHERE Mid$("," & I1.instring & "," ,S1.num , 1 ) = ','
and Mid$("," & I1.instring & "," ,S2.num , 1 ) = ','
and S1.num < S2.num
and instr(1,Mid$("," & I1.instring & ",", S1.num + 1 , S2.num - S1.num - 1),",")=0;
[/tt][/blue]
Where "InputStrings" is the name of the table containing your strings and "instring" is the name of the field in that table that has the comma-separated strings to be parsed. This will give you a table with each value in a separate row.

This proves that

A. Bit fiddling is possible in SQL and
B. It is ill-advised in a production system.

 

[debizabor]

Thanks!

 

[SgtJarrow]

The below code will go to a table called Table1, and check each record's "Interest" field. If will do as I described above and then displays all the results in the debug window....that is the part you would have to figure out what to do with.

Public Sub GetInterests()

    Dim rs As Recordset
    Dim strInterests As String
    Dim strTemp As String
    Dim astrInterests(14, 1) As String
    Dim i As Integer
    
    Set rs = New ADODB.Recordset
    
    With rs
        .Open "Table1", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
        .MoveFirst
        Do Until .EOF
            strInterests = .Fields("Interests") & ","
            Do Until strInterests = ""
                strTemp = Left(strInterests, InStr(1, strInterests, ",") - 1)
                strInterests = Mid(strInterests, InStr(1, strInterests, ",") + 2)
                For i = 0 To 14
                    If IsNull(astrInterests(i, 0)) Or astrInterests(i, 0) = "" Then
                        astrInterests(i, 0) = strTemp
                        astrInterests(i, 1) = 1
                        Exit For
                    ElseIf astrInterests(i, 0) = strTemp Then
                        astrInterests(i, 1) = astrInterests(i, 1) + 1
                        Exit For
                    End If
                Next i
            Loop
            .MoveNext
        Loop
        .Close
    End With
    
    For i = 0 To 14
        Debug.Print astrInterests(i, 0) & " - " & astrInterests(i, 1)
    Next i

End Sub

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com