Clearing Hidden Characters

  • Thread starter Thread starter Kirk P.
  • Start date Start date
K

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?
 
looks like you have a Carriage return...

Try this


replace(insert_String_here,Chr(10),"")
 
Rob Wills said:
looks like you have a Carriage return...

Try this


replace(insert_String_here,Chr(10),"")
Click to expand...


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.
 
Back
Top