Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2010
    Posts
    4

    Red face Unanswered: Syntax error (missing operator) in query expression '23/04/2010 23:37:00'.

    Hi everyone, I need some HEEELLLPPP
    I get the above error when I try to run the qrySQL2 query.
    What I want to do is: if the date from the TimeCalled field is the same as the date in the TimeOut field then compare that the time from TimeOut is later than the time from the TimeCalled field.
    If the date in the TimeOut field is later than the date from the TimeCalled field then do not compare the times.
    Both TimeCalled and TimeOut are Date/Time fields and the format is set to General Date.

    If anyone can help me it would be greatly appreciated.


    Dim qrySQL1 As String
    Dim qrySQL2 As String
    Dim newValue1 As String
    Dim dateTimeOut As Date


    If IsNull(TimeOut) Then 'Vehicle is available
    newValue1 = "-1"
    Else
    newValue1 = "0" 'Vehicle is not available
    dateTimeOut = Format(Now(), "dd/mm/yyyy ") + Format(TimeOut)
    qrySQL1 = "UPDATE tblAppliances SET IsAvailable=" + newValue1 + " WHERE ID=" + Format(Appliance.Value)
    qrySQL2 = "UPDATE tblResponse SET TimeOut=" + Format(dateTimeOut) + " Where ID=" + Format(Me.ID)
    DoCmd****nSQL (qrySQL1)
    DoCmd****nSQL (qrySQL2)

    End If


    If Date1BeforeDate2(TimeOut, TimeCalled) Then
    Exit Sub
    End If

    If (TimeOut) < (TimeCalled) Then
    MsgBox "The TIME OUT is earlier than the TIME CALLED " & vbNewLine & " " & _
    vbCrLf & "Please enter the Correct Time. ", vbOKOnly, " Incorrect Time Entered "
    Cancel = True
    TimeOut.SelStart = 0
    TimeOut.SelLength = Len(TimeOut.Text)
    End If

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    In Access a date/time value would need to be surrounded by #. Try:

    qrySQL2 = "UPDATE tblResponse SET TimeOut=#" + Format(dateTimeOut) + "# Where ID=" + Format(Me.ID)
    Paul

  3. #3
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Moreover, the only accepted format for a date in a query is mm/dd/yyyy (month/day/year).
    Have a nice day!

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Good point; I forget about that since I normally use that format anyway.
    Paul

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by Sinndho View Post
    Moreover, the only accepted format for a date in a query is mm/dd/yyyy (month/day/year).
    I think that's really dependant on the regional settings on the PC. Access will allow for and accept different date formats - since the format is for display purposes only. The actual data is stored as a serial date (32179.6 or some such)
    Inspiration Through Fermentation

  6. #6
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    The regional settings on the PC does not count at all in a query, only the mont/day/year format is valid.
    Have a nice day!

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    It's because the string is being built in VBA:

    ACC2000&#58; How to Format Dates as U.S. Dates Regardless of Regional Settings

    Specifically:

    When you use Visual Basic for Applications to concatenate dates into an SQL string, you must use a standard U.S. date format, regardless of the region that you have set in the Regional Settings tool in Control Panel.
    Paul

  8. #8
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Also from: Microsoft Office Access 2003 Visual Basic Reference.

    "Use International Date Formats in SQL Statements

    You must use English (United States) date formats in SQL statements in Visual Basic. However, you can use international date formats in the query design grid.

    &#169;2003 Microsoft Corporation. All rights reserved".
    Have a nice day!

  9. #9
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    geez.... I'm glad I live in the US and don't have to worry about when to and when not to make conversions
    Inspiration Through Fermentation

  10. #10
    Join Date
    Apr 2010
    Posts
    4
    Quote Originally Posted by pbaldy View Post
    In Access a date/time value would need to be surrounded by #. Try:

    qrySQL2 = "UPDATE tblResponse SET TimeOut=#" + Format(dateTimeOut) + "# Where ID=" + Format(Me.ID)
    That fixed it thanks very much

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help.
    Paul

  12. #12
    Join Date
    Apr 2010
    Posts
    4
    Quote Originally Posted by Sinndho View Post
    Moreover, the only accepted format for a date in a query is mm/dd/yyyy (month/day/year).
    Access does accept the dd/mm/yyyyy format. This format has not been a problem. I believe that Access uses the format the the system date is set too.

  13. #13
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    I believe that Access uses the format the the system date is set too.
    Sorry but you're wrong.
    Have a nice day!

Posting Permissions

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