Hi: Can any one please tell me what i do changes in the query so that the in clause will be shows with comma when i select multiple location as right now it is showing in clause like that:
user_view.User_Office_Location in ('PASC|INSS|NYML|FLWP|')
Required in clause
user_view.User_Office_Location in ('PASC','INSS','NYML','FLWP')
and when i select one location then in ('PASC')
I try to use the replace function AssignID = Replace(AssignID, "|", ",") but its not showing the way which i need
ORIGINAL QUERY in the program
SQL = "select distinct(dbtr_view.debtor_id), dbtr_view.Next_Contact_Date,debt.clt_id, clt_ref_no, name1, name2,
assuming that you are using VB6, and that the In clause is available as a string,
dim strInClause as string
strInClause = "'PASC|INSS|NYML|FLWP|'"
Note that there's an extra separator symbol at the end. Plus, you need to eliminate the existing apostrophe characters. So, first remove the apostophes, then remove any trailing or leading (non SEPARATING 'separators'), SPLIT the string into an array of string, pad each substring with leading/trailing apostrophes, and finally JOIN thearray elements into a single string.
strClauses() as String ' Dynamic array of string to hold the individual clauses...
Dim N as Integer
strInClause = Trim (strInClause) ' Remove leading/trailing spaces
strInClause = replace(strInClause, "'", "") ' remove apostrophes
Do while InstrRev(strInClause, "|") = Len(StrInCLause) ' Do While the last character is a "|"
strInClause = Left(strInClause, Len(StrInClause)-1) ' Remove the last character
Do while InstrRev(strInClause, "|") = 1 ' Do While the First character is a "|" (possibly not needed)
strInClause = Right(strInClause, Len(StrInClause)-1) ' Remove the First character
' Split into an array
strClauses = SPLIT (strInClause, "|")
' Now we have an array of clauses
For N = 0 to UBound(strClauses)
strClauses(N) = "'" & strClauses & "'" ' Pad the individual clause elements with apostrophes...
' Finally, join them back together, using a comma as a delimiter
strInClause = JOIN (strCLauses, ",")
Last edited by loquin; 02-14-07 at 17:37.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert