Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2006
    Posts
    3

    Unanswered: Problem with DATES

    Hi,

    I'm trying to create a search form in MS Access 2000 to search for documents from a documents' register by their creation date. But I just can't get it working. The code in visual basic looks like this:

    Private Sub runquery_Click()

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strStart As Date
    Dim strSQL As String
    Set db = CurrentDb
    Set qdf = db.QueryDefs("testquery")

    'strStart gets a value from a text box
    strStart = Me.start.Value

    strSQL = "SELECT PL.* FROM PL WHERE PL.docdate = #" & strStart & "#;"
    qdf.SQL = strSQL
    DoCmd.OpenQuery "testquery"
    Set qdf = Nothing
    Set db = Nothing

    End Sub

    On my PC and in Access I use date formation dd.mm.yyyy

    When I enter a date into the text box I get following error:
    Syntax error in date in query expression 'PL.docdate=#03.02.2006#'

    When I simplified the code it worked:

    Private Sub runquery_Click()

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Set db = CurrentDb
    Set qdf = db.QueryDefs("testquery")

    strSQL = "SELECT PL.* FROM PL WHERE (PL.docdate = [Forms]![test]![start]);"
    qdf.SQL = strSQL
    DoCmd.OpenQuery "testquery"
    Set qdf = Nothing
    Set db = Nothing

    End Sub

    But I want to use the strStart operator in SQL statment. By the way I also tried using the MS Date and Time Picker and Calendar Control, but without luck, I still got the same error message.

    Can anyone help with this issue, I would be really grateful, I've been messing with it for several days.

    Erkki

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    In VBA Access does dates in MM/DD/YYYY format ... So, if you format your date string accordingly when using to build your filter clause you might have better luck ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Aug 2006
    Posts
    3
    Is there some work around that I could use dd.mm.yyyy formation to insert the dates and why doesn't the Microsoft Date and Time Picker work shouldn't the Access understand it's values?

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by entex
    Is there some work around that I could use dd.mm.yyyy formation to insert the dates and why doesn't the Microsoft Date and Time Picker work shouldn't the Access understand it's values?
    Nope. When using date literals, Access does MM/DD/YYYY ... You're stuck with it.
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Aug 2006
    Posts
    3
    OK, but this formation (mm/dd/yyyy) only works when I describe strStart As String, when I describe it As Date I get the same error. Access converts it automatically to dd/mm/yyyy formation even if I enter it in mm/dd/yyyy formation and therefore I get the same error???

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Try Using this line

    strSQL = "SELECT PL.* FROM PL WHERE (PL.docdate = #" & format([Forms]![test]![start],"mm/dd/yy") & "#);"
    and see what happens.

    This is a perenial problem with UK dates.

    MTB

  7. #7
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    One step further

    strStart = format$(Me.start.Value, "yyyy-mm-dd") ' which is ISO 8601 or
    strStart = format$(Me.start.Value, "mm\/dd\/yyyy")
    ' see the following link for info on this one
    ' http://allenbrowne.com/ser-36.html
    strSQL = "SELECT PL.* FROM PL WHERE PL.docdate = #" & strStart & "#;"

    And yes, for that to work, strStart must be a string, else do the formatting directly into the sql string

    strSQL = "SELECT PL.* FROM PL WHERE PL.docdate = #" & format$(Me.start.Value, "mm\/dd\/yyyy") & "#;"
    Roy-Vidar

  8. #8
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8
    I Use this little function

    Code:
    Function USA(ThisDate as date)
        USA= month(ThisDate) & "/" & Day(ThisDate) & "/" & year(ThisDate)
    End Function
    use like
    strSQL = "SELECT PL.* FROM PL WHERE PL.docdate = #" & USA(Me.start.Value) & "#;"

    Been a Kiwi we like the date dd/mm/yyyy
    but SQL always want mm/dd/yyyy
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •