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),"")


"Kirk P." wrote:

> 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" <(E-Mail Removed)> wrote in message
news:804A5033-A6D5-49AE-A21C-(E-Mail Removed)...
> 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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)