Single string to be split and queried 1

[M8KWR]

I have a string, example "1; 2; 3;"

I need to split the above down to get the text before the ";" so in this example it would have 3 outcomes.

Then i need to query the results against another table to get another answer for the output.

So if the other table was
1 - Red
2 - Orange
3 - Blue

Then the output of the module would be

Red, Orange, Blue

I am unsure where the start in order to the figure this out.

Any help or advise would be appreciated.

 

[HarleyQuinn]

Hmm, was going to suggest using Split() for this but decided I'd go down a different route (there are many ways of doing this, I've chosen one that's a bit different than I would normally use, Split() couild also be used within this function quite easily). Have a look at this:

Public Function GetDBValueString(strInput As String) As String
    
    Dim rs As Recordset
    Dim DB As Database
    Dim strTemp As String
    
    strInput = Replace(strInput, "; ", ",")
    
    strInput = Left(strInput, Len(strInput) - 1)
    
    Set DB = CurrentDb
    
    Set rs = DB.OpenRecordset("SELECT YourField FROM YourTable, IDName WHERE (((YourOtherField) In (" & strInput & ")));")
    
    While Not rs.EOF
    
        strTemp = strTemp & rs.Fields("YourField") & ","
        rs.MoveNext
    
    Wend

    GetDBValueString = Left(strTemp, Len(strTemp) - 1)
    
    rs.Close
    Set rs = Nothing
    DB.Close
    Set DB = Nothing

End Function

If you need anything clarifying please post back.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[M8KWR]

Many thanks for the quick response.

Just what i required.

 

[HarleyQuinn]

Glad I could help, thanks for the star

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com