Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jul 2001
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Querying a SQL Server using a date on a Form (AccessXP/SQL Server 2000)

    I have a Form (not a data access page) in an Access project that contains a date field. I want to use the value of that date field in a SQL string that will be passed to a SQL Server 2000 database. I cannot seem to get the portion of the query containing the date parameter to work.

    Here is the SQL string:

    strSQLUnitsInspected = "SELECT ProjectUnitAddress " & _
    "FROM qryInspectionProjectUnits " & _
    "WHERE (ProjectID = " & frmProjectInspectionReport.ProjectID & ")" & _
    " AND (InspectionDate = CONVERT(DATETIME, " & frmProjectInspectionReport.InspectionDate & ", 102))"

    I keep getting one of two errors, either no records have been returned, or there is a syntax error near the #.

    I know Access usually sandwiches dates in between # signs, so I am guessing that somehow I am not getting the date information properly formatted in the SQL string.

    Thanks in advance for the help!

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Querying a SQL Server using a date on a Form (AccessXP/SQL Server 2000

    SQL Server expects dates to be between single quotes, a la '1/1/2003'
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  3. #3
    New Lounger
    Join Date
    Jul 2001
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Querying a SQL Server using a date on a Form (AccessXP/SQL Server 2000

    Hi Shane,

    Thanks! That did the trick

Posting Permissions

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