Build PL/SQL Statement with VBA

[Kirk P.]

I'm building a SQL statement programatically which is run via a pass-through
query to an Oracle database. I've got this code, which handles the case
where a user wants to search for specific order ID number(s):

If chkOrder And Me.cboOper = "IN" Then
strWHEREc = strWHEREc & " AND sd.requisition_id " & [cboOper] &
" ('" & [txtOrder] & "') "
Else
strWHEREc = strWHEREc & " AND sd.requisition_id " & [cboOper] &
" '" & [txtOrder] & "' "
End If

In cboOper, the user can choose IN or =. If they choose IN, what I would
like is the ability for them to enter several order ID numbers separated by
commas. The trouble is in Oracle the order ID is a text field, so the
numbers must be enclosed in apostrophies. The user can supply the commas,
but I would prefer the code to supply the apostrophies. My current code
delivers:

IN ('123456789, 576535658')

I need - IN ('123456789', '576535658')

If I leave the apostrophies out, Oracle returns a data type error. Any ideas?

 

[dymondjack]

Try making a function that parses the user input, using the comma as the
delimiter. For each parsed portion of the code, add an apostrophe to the
beginning and end, and re-contencate it. You can do all this using the Instr
Mid and Len functions.

I started writing a function up for this but I don't have time to finish it
today. If you don't get anywhere within the next few days I'll see if I can
get it wrapped up, but that's definately the direction I would go with it
(I've done alot of this type of string manipulation for machine tool
programming applications).


--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery