Find the position of the Nth occurence in a string

[lala]

VarChar is text, right? then the ids are stored as text in Oracle. anyhow, the 's are not the problem anymore, everything is working perfect. I'm going to try automating the conversion part, but it's not a big deal

I thought of writing to another cell after a certain number of characters, but i think i'd rather write to word or just create the macro in word, will think about it.

thank you so much for all the ideas, i learned so much from this thread

 

[lala]

Private Sub Test_Click()
Dim fnGetAccounts As String
Dim TheSSN As Long

Dim Temp As String
Dim rst As DAO.Recordset

Temp = ""

Set rst = CurrentDb.OpenRecordset("Select * From YOURTABLE")
While Not rst.EOF And Not rst.BOF
   Temp = Temp & rst!YOURFIELDTOCONCAT & ","
   rst.MoveNext
   Wend
fnGetAccounts = left(Temp, Len(Temp) - 1)
MsgBox fnGetAccounts

Debug.Print fnGetAccounts

End Sub

in case anyone needs it, the code is not mine, i edited some code i got off the internet

this code takes the IDs from a table and puts them in a row separated by commas

 

[Timax]

and one more reatrted thing i did, when posting this guy's function i deleted all the comments, trying to make it shorter for you, guys, to read. He explains why that line is there, here's the original version

Public Function CharPos(SearchString As String, Char As String, Instance As Long)
     'Function purpose:  To return the position of the (first character of the )
     'nth occurance of a specific character set in a range
     
    Dim x As Long, n As Long
     
     'Loop through each letter in the search string
    For x = 1 To Len(SearchString)
         'Increment the number of characters search through
        CharPos = CharPos + 1
         
         'check if the next character(s) match the text being search for
         'and increase n if so (to count how many matches have been found
        If Mid(SearchString, x, Len(Char)) = Char Then n = n + 1
         
         'Exit loop if instance matches number found
        If n = Instance Then Exit Function
    Next x
     
     'The error below will only be triggered if the function was not
     'already exited due to success
    CharPos = CVErr(xlErrValue)
     
End Function

Very nice solution!!! thank you all. Can you guys modify it the way that it will start searching from the end of the string instead of from the beginning?

 

[Timax]

Very nice solution!!! thank you all. Can you guys modify it the way that it will start searching from the end of the string instead of from the beginning?

Never mind, I added STEP -1 and all worked Thank you