A
Anna
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,
list_date, cur_bal, assign_id, dbtr_view.status_code,
dbtr_view.status_date, dbtr_view.next_contact_date " & _
"From dm.dbtr_view " & _
"left outer join dm.debt on (debt.debt_id =
dbtr_view.debtor_id,0) " & _
"inner join dm.user_view on user_view.user_id =
dbtr_view.assign_id " & _
"left outer join dm.pmt on (dbtr_view.debtor_id =
pmt.debtor_id,0) where "
SQL = SQL & "user_view.User_Office_Location in ('" & AssignID
& "')"
debug.print SQL
select distinct(dbtr_view.debtor_id),
dbtr_view.Next_Contact_Date,debt.clt_id, clt_ref_no, name1, name2,
list_date, cur_bal,
assign_id, dbtr_view.status_code, dbtr_view.status_date,
dbtr_view.next_contact_date From dm.dbtr_view left outer join
dm.debt on (debt.debt_id = dbtr_view.debtor_id,0) inner join
dm.user_view on user_view.user_id = dbtr_view.assign_id left
outer join dm.pmt on (dbtr_view.debtor_id = pmt.debtor_id,0) where
user_view.User_Office_Location in ('PASC|INSS|NYML|FLWP|')
REQUIRED QUERY
select distinct(dbtr_view.debtor_id),
dbtr_view.Next_Contact_Date,debt.clt_id, clt_ref_no, name1, name2,
list_date, cur_bal,
assign_id, dbtr_view.status_code, dbtr_view.status_date,
dbtr_view.next_contact_date From dm.dbtr_view left outer join
dm.debt on (debt.debt_id = dbtr_view.debtor_id,0) inner join
dm.user_view on user_view.user_id = dbtr_view.assign_id left
outer join dm.pmt on (dbtr_view.debtor_id = pmt.debtor_id,0) where
user_view.User_Office_Location in ('PASC','INSS','NYML','FLWP')
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,
list_date, cur_bal, assign_id, dbtr_view.status_code,
dbtr_view.status_date, dbtr_view.next_contact_date " & _
"From dm.dbtr_view " & _
"left outer join dm.debt on (debt.debt_id =
dbtr_view.debtor_id,0) " & _
"inner join dm.user_view on user_view.user_id =
dbtr_view.assign_id " & _
"left outer join dm.pmt on (dbtr_view.debtor_id =
pmt.debtor_id,0) where "
SQL = SQL & "user_view.User_Office_Location in ('" & AssignID
& "')"
debug.print SQL
select distinct(dbtr_view.debtor_id),
dbtr_view.Next_Contact_Date,debt.clt_id, clt_ref_no, name1, name2,
list_date, cur_bal,
assign_id, dbtr_view.status_code, dbtr_view.status_date,
dbtr_view.next_contact_date From dm.dbtr_view left outer join
dm.debt on (debt.debt_id = dbtr_view.debtor_id,0) inner join
dm.user_view on user_view.user_id = dbtr_view.assign_id left
outer join dm.pmt on (dbtr_view.debtor_id = pmt.debtor_id,0) where
user_view.User_Office_Location in ('PASC|INSS|NYML|FLWP|')
REQUIRED QUERY
select distinct(dbtr_view.debtor_id),
dbtr_view.Next_Contact_Date,debt.clt_id, clt_ref_no, name1, name2,
list_date, cur_bal,
assign_id, dbtr_view.status_code, dbtr_view.status_date,
dbtr_view.next_contact_date From dm.dbtr_view left outer join
dm.debt on (debt.debt_id = dbtr_view.debtor_id,0) inner join
dm.user_view on user_view.user_id = dbtr_view.assign_id left
outer join dm.pmt on (dbtr_view.debtor_id = pmt.debtor_id,0) where
user_view.User_Office_Location in ('PASC','INSS','NYML','FLWP')