Bob Barrows [MVP]
7/17/2008 8:08:00 PM
Old Pedant wrote:
>> Also, I don't know SQL particularly, but your query looks a bit
>> suspect to me. It seems to be missing quote marks around the name of
>> the CVS file (assuming it is intended as a literal) and the use of
>> the DateValue and Date() functions in the middle of a literal query
>> string seems incorrect - shouldn't their results be concatenated
>> into the string?
>
> No, all those are correct. Remember, this is using the JET OLEDB
> driver, and it uses the Access dialect of SQL. In particular, that
> dialect is based on VBA and its functions. So indeed most functions
> you find in VBScript are also available in that dialect of SQL. Yes,
> most definitely including DATEVALUE( ) and DATE( ) [and LEFT() and
> MID() and many, many more.] SQL is not a highly standardized
> language, unfortunately. T-SQL, the dialect used by SQL Server, has
> no equivalent of DATEVALUE( ) or DATE( ), just as a "for instance".
> And in MySQL, the DATE( ) function is actually the equivalent of
> DATEVALUE( ) in Access SQL.
>
Pardon the pedantry :-)
but not all VBA functions are usable in JetSQL when it is executed by
external applications. The most obvious example I can think of right now
is the Nz() function, which needs to be replaced by IIF() when executed
via ADO. If you want a more complete list, search the MS Knowledge Base
for "Jet" and "sandbox".
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.