ADP SQL DATE SYNTAX PROBLEM

[Dan]

Hello. I have just converted an MDB database to and ADP project (2003) with
a SQL 2000 back end. I'm running into a particular problem that has got me
stumped.

I have some SQL INSERT queries with dates, which is normally in the syntax
#date# ... this is kicking out an error in the ADP project. It doesn't like
the pound sign, but when I remove it, the query doesn't work.

Help!

 

[Dave Patrick]

Try passing them in as stings. Something like;

WHERE (tbl_Prod_SampTons.ShiftDate <= CONVERT(DATETIME, '" & date5 & "',
102))

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Hello. I have just converted an MDB database to and ADP project (2003)
with
| a SQL 2000 back end. I'm running into a particular problem that has got
me
| stumped.
|
| I have some SQL INSERT queries with dates, which is normally in the syntax
| #date# ... this is kicking out an error in the ADP project. It doesn't
like
| the pound sign, but when I remove it, the query doesn't work.
|
| Help!

 

[Alex Dybenko]

Hi,
even better to pass dates in international format for SQL Server:
'yyyymmdd'

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Dan]

Ok, this is bizare. First off, I'm not really sure where I should be
executing the function below. VBA doesn't recognize CONVERT, so I'm assuming
it should be passed along in the SQL string, but I don't know what the '102'
represents.

Also, and here's the strange thing, I got this to work when I use CDbl(date)
within VBA and CDbl(Time) EXCEPT that CDbl(date) always returns a date two
days ahead of today, which means I need to use CDbl(Date)-2. I don't
understand this.

 

[Dave Patrick]

Take a look at CONVERT in books on line. Also create a query in enterprise
manager and specify a where clause for the datetime type and look at the
resulting SQL and all should become somewhat clearer.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Ok, this is bizare. First off, I'm not really sure where I should be
| executing the function below. VBA doesn't recognize CONVERT, so I'm
assuming
| it should be passed along in the SQL string, but I don't know what the
'102'
| represents.
|
| Also, and here's the strange thing, I got this to work when I use
CDbl(date)
| within VBA and CDbl(Time) EXCEPT that CDbl(date) always returns a date two
| days ahead of today, which means I need to use CDbl(Date)-2. I don't
| understand this.

 

[Dan]

Cool. I modified my INSERT query and it worked perfectly! Thanks!

 

[Dave Patrick]

That's good to hear. You're welcome.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Cool. I modified my INSERT query and it worked perfectly! Thanks!