ANSQLWherein

Creates list of items to be used in ' Where item in (List)' list
Mainly adds the' at start and end, and makes sure each item is enclosed with '
Converts ...
tem1,item2, item45 , item4, someother ,another
Into ...
'tem1','item2','item45','item4','someother','another'

CodeFunctionName
What is this?

Public

Tested

Original Work
Function ANSQLWherein(StringList)
' Convert Stringlist into list that is ready to be passed in IN statement as strings
' 'anmar','sql','excel','function'
' Also, removes blanks, unneeded spaces
' Converts ...
' tem1,item2, item45 , item4, someother ,another
' Into ...
' 'tem1','item2','item45','item4','someother','another'
' And
' " tem1 ,item2, item45 po,455k , item4, someother ,another , ,|,85rtt, {{C}},uur"
' To ...
' 'tem1','item2','item45 po','455k','item4','someother','another','|','85rtt','{{C}}','uur'
'
Rett = Trim(StringList)
SepaTemp = "|"
If InStr(1, Rett, SepaTemp) > 0 Then SepaTemp = "(C)"
If InStr(1, Rett, SepaTemp) > 0 Then SepaTemp = "{C}"
If InStr(1, Rett, SepaTemp) > 0 Then SepaTemp = "{{C}}"
If InStr(1, Rett, SepaTemp) > 0 Then SepaTemp = "{[$C$]}"
SepaTempQ = "'" & SepaTemp & "'"
Rett = Replace(Rett, "'", "'") ' REM Convert ' into '
Rett = Replace(Rett, " ", " ")
Rett = Replace(Rett, " ", " ")
Rett = Replace(Rett, " ", " ")
Rett = Replace(Rett, " ", " ")
Rett = Replace(Rett, " ", " ")
Rett = Replace(Rett, ", ,", ",")
Rett = Replace(Rett, " , ", SepaTempQ)
Rett = Replace(Rett, " ,", SepaTempQ)
Rett = Replace(Rett, ", ", SepaTempQ)
Rett = Replace(Rett, ",", SepaTempQ)
Rett = Replace(Rett, SepaTemp, ",")
ANSQLWherein = "'" & Rett & "'" & vbCrLf
End Function

StringList

Views 1,262

Downloads 409

CodeID
DB ID