PC Review


Reply
Thread Tools Rate Thread

ADP SQL DATE SYNTAX PROBLEM

 
 
=?Utf-8?B?RGFu?=
Guest
Posts: n/a
 
      4th Jul 2006
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!
 
Reply With Quote
 
 
 
 
Dave Patrick
Guest
Posts: n/a
 
      4th Jul 2006
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!


 
Reply With Quote
 
 
 
 
Alex Dybenko
Guest
Posts: n/a
 
      4th Jul 2006
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!
>
>


 
Reply With Quote
 
=?Utf-8?B?RGFu?=
Guest
Posts: n/a
 
      4th Jul 2006
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!
>
>
>

 
Reply With Quote
 
Dave Patrick
Guest
Posts: n/a
 
      4th Jul 2006
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.


 
Reply With Quote
 
=?Utf-8?B?RGFu?=
Guest
Posts: n/a
 
      4th Jul 2006
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.
>
>
>

 
Reply With Quote
 
Dave Patrick
Guest
Posts: n/a
 
      5th Jul 2006
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!


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
I've linked a ADP to SQL Database now I can't edit the ADP Table =?Utf-8?B?SGlkZXM=?= Microsoft Access 3 17th May 2006 10:01 PM
How to get SQL Syntax Highlighting in Access2003 adp? =?Utf-8?B?U2hhbm5vbiBWYWxlbnRpbmU=?= Microsoft Access Queries 0 22nd Jun 2005 07:56 AM
How to control ADP Connection when start ADP =?Utf-8?B?TmlraQ==?= Microsoft Access 0 11th Nov 2004 10:12 AM
Re: ADP routine fails in ADP. Vadim Rapp Microsoft Access ADP SQL Server 0 7th Jul 2003 06:19 PM
Re: ADP routine fails in ADP. BJ Freeman Microsoft Access ADP SQL Server 0 7th Jul 2003 04:01 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:59 PM.