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|NYM L|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(AssignI D, "|",
",") but its not showing the way which i need
ORIGINAL QUERY in the program
SQL = "select distinct(dbtr_v iew.debtor_id),
dbtr_view.Next_ Contact_Date,de bt.clt_id, clt_ref_no, name1, name2,
list_date, cur_bal, assign_id, dbtr_view.statu s_code,
dbtr_view.statu s_date, dbtr_view.next_ contact_date " & _
"From dm.dbtr_view " & _
"left outer join dm.debt on (debt.debt_id =
dbtr_view.debto r_id,0) " & _
"inner join dm.user_view on user_view.user_ id =
dbtr_view.assig n_id " & _
"left outer join dm.pmt on (dbtr_view.debt or_id =
pmt.debtor_id,0 ) where "
SQL = SQL & "user_view.User _Office_Locatio n in ('" & AssignID
& "')"
debug.print SQL
select distinct(dbtr_v iew.debtor_id),
dbtr_view.Next_ Contact_Date,de bt.clt_id, clt_ref_no, name1, name2,
list_date, cur_bal,
assign_id, dbtr_view.statu s_code, dbtr_view.statu s_date,
dbtr_view.next_ contact_date From dm.dbtr_view left outer join
dm.debt on (debt.debt_id = dbtr_view.debto r_id,0) inner join
dm.user_view on user_view.user_ id = dbtr_view.assig n_id left
outer join dm.pmt on (dbtr_view.debt or_id = pmt.debtor_id,0 ) where
user_view.User_ Office_Location in ('PASC|INSS|NYM L|FLWP|')
REQUIRED QUERY
select distinct(dbtr_v iew.debtor_id),
dbtr_view.Next_ Contact_Date,de bt.clt_id, clt_ref_no, name1, name2,
list_date, cur_bal,
assign_id, dbtr_view.statu s_code, dbtr_view.statu s_date,
dbtr_view.next_ contact_date From dm.dbtr_view left outer join
dm.debt on (debt.debt_id = dbtr_view.debto r_id,0) inner join
dm.user_view on user_view.user_ id = dbtr_view.assig n_id left
outer join dm.pmt on (dbtr_view.debt or_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|NYM L|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(AssignI D, "|",
",") but its not showing the way which i need
ORIGINAL QUERY in the program
SQL = "select distinct(dbtr_v iew.debtor_id),
dbtr_view.Next_ Contact_Date,de bt.clt_id, clt_ref_no, name1, name2,
list_date, cur_bal, assign_id, dbtr_view.statu s_code,
dbtr_view.statu s_date, dbtr_view.next_ contact_date " & _
"From dm.dbtr_view " & _
"left outer join dm.debt on (debt.debt_id =
dbtr_view.debto r_id,0) " & _
"inner join dm.user_view on user_view.user_ id =
dbtr_view.assig n_id " & _
"left outer join dm.pmt on (dbtr_view.debt or_id =
pmt.debtor_id,0 ) where "
SQL = SQL & "user_view.User _Office_Locatio n in ('" & AssignID
& "')"
debug.print SQL
select distinct(dbtr_v iew.debtor_id),
dbtr_view.Next_ Contact_Date,de bt.clt_id, clt_ref_no, name1, name2,
list_date, cur_bal,
assign_id, dbtr_view.statu s_code, dbtr_view.statu s_date,
dbtr_view.next_ contact_date From dm.dbtr_view left outer join
dm.debt on (debt.debt_id = dbtr_view.debto r_id,0) inner join
dm.user_view on user_view.user_ id = dbtr_view.assig n_id left
outer join dm.pmt on (dbtr_view.debt or_id = pmt.debtor_id,0 ) where
user_view.User_ Office_Location in ('PASC|INSS|NYM L|FLWP|')
REQUIRED QUERY
select distinct(dbtr_v iew.debtor_id),
dbtr_view.Next_ Contact_Date,de bt.clt_id, clt_ref_no, name1, name2,
list_date, cur_bal,
assign_id, dbtr_view.statu s_code, dbtr_view.statu s_date,
dbtr_view.next_ contact_date From dm.dbtr_view left outer join
dm.debt on (debt.debt_id = dbtr_view.debto r_id,0) inner join
dm.user_view on user_view.user_ id = dbtr_view.assig n_id left
outer join dm.pmt on (dbtr_view.debt or_id = pmt.debtor_id,0 ) where
user_view.User_ Office_Location in ('PASC','INSS', 'NYML','FLWP')