What am I doing wrong?

I don't know the Oracle flavour of SQL, but I believe that the standard
representation of a date in SQL is #<date>#.  I would try making your
parameter a string like ="#" & TEXT(TheDate,"d-mmm-yyyy") & "#"

Let us know if you find a solution

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

In Oracle you have a "default" date format associated with your session.  That
means that if you pass it a date in that format it will understand it.  If you
pass a date in any other format you must include a format string to tell the
Oracle server what you mean.  In theory the ODBC driver may convert dates for you,
but in practice this doesn't always work and can slow things down considerably.

In practice the only really safe thing to do is always include a format string in
your SQL statement.

So, for example, this is how I would use a date in a SQL statement

select
<columns list...>
from
<table list.....>
where
<other predicates...>
and <date_field> = to_date('28-jan-2002','dd-mon-yyyy')

The 'to_date' is an Oracle function that takes a character string containing a
date (or a date and time) and a second character string which gives the format.
Its fairly similar to the VB/VBA format command but the picture strings are
different.  Also both strings should be in single quotes.

If you are returning dates from Oracle then its often a good idea to do the
reverse of this procedure.  Using the Oracle to_char function to convert a date
into a string that will be unambiguous when it gets back to VB/VBA.  Otherwise you
can get wrong interpretations of dates in your results.

Hope this helps.

Jim Markham