Clearing Hidden Characters

[Kirk P.]

I've got VBA written that supplies the SQL for a pass-through query. The
user supplies the order numbers they want to search for (separated by
commas), and the SQL is written. The WHERE clause contains this VBA code:

division_order_id IN " & "('" & Replace([txtOrder], ",", "','") & "') "

The intention here is to clear out any spaces that may be contained in the
string. When the SQL is written to the pass-through query, the WHERE clause
looks like this:

division_order_id IN ('142211238','
142211662')

Oracle only finds the first item. However if I simply hit the delete key
just after the 3rd apostrophe, the SQL changes to this

division_order_id IN ('142211238','142211662')

There appears to be some hidden character that's causing the line break.
How do I identify and get rid of these "hidden" characters?

 

[Rob Wills]

looks like you have a Carriage return...

Try this


replace(insert_String_here,Chr(10),"")

 

[Dirk Goldgar]

looks like you have a Carriage return...

Try this


replace(insert_String_here,Chr(10),"")

Chr(10) is a line feed, not a carriage return. That could still be what the
"hidden" characters are, so if the above replacement does the job, great.
If it doesn't, try replacing Chr(13) instead, or as well.