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!

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

"Dan" wrote:
| 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!

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

"Dave Patrick" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
> "Dan" wrote:
> | 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!
>
>

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" wrote:

> 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
>
> "Dan" wrote:
> | 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!
>
>
>

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

"Dan" wrote:
| 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.

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

"Dave Patrick" wrote:

> 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
>
> "Dan" wrote:
> | 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.
>
>
>

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

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