ADP SQL DATE SYNTAX PROBLEM

Discussion in 'Microsoft Access VBA Modules' started by Guest, Jul 4, 2006.

  1. Guest

    Guest Guest

    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!
     
    Guest, Jul 4, 2006
    #1
    1. Advertisements

  2. Guest

    Dave Patrick Guest

    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!
     
    Dave Patrick, Jul 4, 2006
    #2
    1. Advertisements

  3. Guest

    Alex Dybenko Guest

    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" <> wrote in message
    news:%...
    > 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!
    >
    >
     
    Alex Dybenko, Jul 4, 2006
    #3
  4. Guest

    Guest Guest

    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!
    >
    >
    >
     
    Guest, Jul 4, 2006
    #4
  5. Guest

    Dave Patrick Guest

    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.
     
    Dave Patrick, Jul 4, 2006
    #5
  6. Guest

    Guest Guest

    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.
    >
    >
    >
     
    Guest, Jul 4, 2006
    #6
  7. Guest

    Dave Patrick Guest

    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!
     
    Dave Patrick, Jul 5, 2006
    #7
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Andrea

    project(.adp) file & SQL Server -- VBA connection?

    Andrea, Oct 29, 2003, in forum: Microsoft Access VBA Modules
    Replies:
    1
    Views:
    505
    Dev Ashish
    Oct 29, 2003
  2. Guest

    sql server/adp sync local copy

    Guest, May 28, 2004, in forum: Microsoft Access VBA Modules
    Replies:
    1
    Views:
    197
    solex
    May 28, 2004
  3. Guest

    ADP - Prevent SQL Errors

    Guest, Jan 7, 2005, in forum: Microsoft Access VBA Modules
    Replies:
    1
    Views:
    179
    Guest
    Jan 7, 2005
  4. WhatTha
    Replies:
    0
    Views:
    358
    WhatTha
    Jan 24, 2006
  5. Guest

    problem with SQL function & decimal in ADP

    Guest, Apr 3, 2006, in forum: Microsoft Access VBA Modules
    Replies:
    1
    Views:
    139
    Guest
    Apr 3, 2006
Loading...

Share This Page