KevTheKiwi
IS-IT--Management
This problem gave me a whole bunch of hassle, so I thought I'd post my solution as it cost me a half day of browsing the ng's
If you're trying to execute an SQL query on SQL Server via MS Access/VBA/ADO/OLEDB that has a date criteria, e.g.
you'll find that the above will execute fine in a jet query to over linked tables, but won't execute via ADO/VBA. You'll probably get error codes and messages like:
OR:
The first error with the 'smalldatetime' will come up if you try and use single quotes to delimit the date value in the SQL string e.g.
The second error will come up if you try to use the hash '#' as a value delimiter much as you do in a standard jet query created via a wizard or whatever
Anyways, the following works a treat:
where datFromDate and datToDate are the date range you need the criteria to match. ignore any newsgroup comments you may get about a date in the data being not a valid date value or outside some 2097 or 1701 or other rubbish year value, they're just red herrings. I found DateValue() was good for stripping the 'xx:xx:xx PM' time values at the end of the Date string that ActiveX control date pickers seem to like to give you and which VBA will seem to mysteriously and intermittently append if you pass two or more Date variables as sub or function arguments.
HTH
ktk
If you're trying to execute an SQL query on SQL Server via MS Access/VBA/ADO/OLEDB that has a date criteria, e.g.
Code:
WHERE AND [table name].[date field]>=#03/04/2002# And [table name].[date field]<=#23/09/2002#you'll find that the above will execute fine in a jet query to over linked tables, but won't execute via ADO/VBA. You'll probably get error codes and messages like:
Code:
-2147217913 The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.OR:
Code:
-2147217900 Line 1: Incorrect syntax near '#'The first error with the 'smalldatetime' will come up if you try and use single quotes to delimit the date value in the SQL string e.g.
Code:
"WHERE AND [table name].[date field]>='" & CStr(DateValue([from date variable])) & "' And [table name].[date field]<='" & CStr(DateValue([from date variable])) & "'"The second error will come up if you try to use the hash '#' as a value delimiter much as you do in a standard jet query created via a wizard or whatever
Anyways, the following works a treat:
Code:
"WHERE [table name].[date field] BETWEEN '" & _
Format(CStr(datFromDate), "mm/dd/yyyy") & "' And '" & _
Format(CStr(datToDate), "mm/dd/yyyy") & "' "where datFromDate and datToDate are the date range you need the criteria to match. ignore any newsgroup comments you may get about a date in the data being not a valid date value or outside some 2097 or 1701 or other rubbish year value, they're just red herrings. I found DateValue() was good for stripping the 'xx:xx:xx PM' time values at the end of the Date string that ActiveX control date pickers seem to like to give you and which VBA will seem to mysteriously and intermittently append if you pass two or more Date variables as sub or function arguments.
HTH
ktk
