Access ADO SQL Date Problem


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: Access ADO SQL Date Problem

  1. #1
    Simon Tonkin Guest

    Access ADO SQL Date Problem


    I have a problem with my SQL Query. The way I created the database under Access97
    was to use last updated (Date and Time) as the primary key. I thought I would
    just be able to use the following statement under ADO to access the data

    "SELECT * FROM COMPANY_DETAILS_BACKUP WHERE LAST_UPDATED = '#03/15/2002 14:09:31#';"

    However, this doesn't seem to work.

    Any help appreciated,

    Simon.

  2. #2
    G. Allen Guest

    Re: Access ADO SQL Date Problem


    If LAST_UPDATED is a date field try removing the single quotes from around
    the date in the WHERE clause.


    G. Allen

    "Simon Tonkin" <Simon_Tonkin@poyntons.com.au> wrote:
    >
    >I have a problem with my SQL Query. The way I created the database under

    Access97
    >was to use last updated (Date and Time) as the primary key. I thought I

    would
    >just be able to use the following statement under ADO to access the data
    >
    >"SELECT * FROM COMPANY_DETAILS_BACKUP WHERE LAST_UPDATED = '#03/15/2002

    14:09:31#';"
    >
    >However, this doesn't seem to work.
    >
    >Any help appreciated,
    >
    >Simon.



  3. #3
    Steve Mudge Guest

    Re: Access ADO SQL Date Problem


    "Simon Tonkin" <Simon_Tonkin@poyntons.com.au> wrote:
    >
    >I have a problem with my SQL Query. The way I created the database under

    Access97
    >was to use last updated (Date and Time) as the primary key. I thought I

    would
    >just be able to use the following statement under ADO to access the data
    >
    >"SELECT * FROM COMPANY_DETAILS_BACKUP WHERE LAST_UPDATED = '#03/15/2002

    14:09:31#';"
    >
    >However, this doesn't seem to work.
    >
    >Any help appreciated,
    >
    >Simon.


    Hello

    Maybe you can format the data and time stamp back to the serial number! and
    store it as a double
    Steve


  4. #4
    Join Date
    Jan 2010
    Posts
    1

    Help please...

    Did you ever get this thing to work? I know it's about 8 years ago, but i'm now bumping into this problem...

    Please help.

  5. #5
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    What version of Access are you using?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  6. #6
    Join Date
    Jun 2009
    Posts
    135
    Access requires the data to be surrounded by the pound signs (#) while SQL will view that as incorrect data. So, there are a couple of ways in which to make your program usable by both Access and SQL. One is to know your connection string and based upon that you could set a boolean variable that tells you which format to use. Another is to save the date in a numeric field in the format of yyyymmdd and time in another field in whatever format you want...



    Good Luck

  7. #7
    Join Date
    Feb 2004
    Location
    Sydney, Australia
    Posts
    498
    As said in the previous post, with some boolean to indicate whether Access or SQL (pass something in command line?), then you can use the following in your SQL statements:-

    Code:
    Public Const cDateTimeFormatSql = "yyyymmdd h:mm:ss"
    Public Const cDateTimeFormatAccess = "mmm d yyyy h:mm:ss"
    
    (then ...)
    
    If gIsSql = True Then
            MyFormattedDateTime = "'" & Format$(ThisDateTime, cDateTimeFormatSql) & "'"
    Else
            MyFormattedDateTime = "#" & Format$(ThisDateTime, cDateTimeFormatAccess) & "#"
    End If
    This assumes you have already checked 'ThisDateTime' has NO # or ' surrounding it.
    Last edited by gupex; 02-02-2010 at 12:21 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center