none
Problem formatiing Date Parameter for SQL Query RRS feed

  • Question

  • I am developing an Access Database that uses an SQL SERVER back-end. 

    I have written a Query that returns a variable number of 'Diary Notes' for a variable period prior to today.  The Query works fine, provided I run the Query manually and input a Date formatted as dd/mm/yyyy.  I have also set up a Control on my 'Login' Form called [Text - Start Date for Diary Note lists] that contains the required date, formatted as dd/mm/yyyy.  The Sub-Form that displays the Diary Note List has a Query Parameter:     @pStartDate = Forms![Login]![Text - Start Date for Diary Note lists]      

    However when I try to display the Sub-Form, it does not display.  I just get a totally blank Sub-Form.  When I run the Query on which the Sub-Form is based and input 1/3/2016 I just get this Months 'Diary Notes' - as intended.

    I've already tried:

    • Putting single quotes around the Date, because a criteria of > '1/3/2016' worked in the Query.
    • Formatted the date as yyyy/mm/dd and mm/dd/yyyy

    But there was no improvement.  What am I forgetting?

    Wednesday, March 30, 2016 7:02 AM

Answers

  • How do you access the SQL Server backend?

    When you're using linked tables or using the date in an Access query, then you need the US-date format: #mm/dd/yyyy# without quotes. See also Wrong Formatting in Code.

    When you're working server-side, then you need a SQL Server date literal. Use 'yyyymmdd' with single quotes.

    • Marked as answer by David_JunFeng Thursday, April 7, 2016 9:12 AM
    Wednesday, March 30, 2016 11:53 AM
  • Is the parameter declared as DATETIME?  If not it might well be misinterpreted as an arithmetic expression.

    Stefan is quite right about date literals, as entering a date literal in UK format would change 4th July to 7th April.  I always use the ISO standard for date notation of YYYY-MM-DD.  In this case, however, you are referencing a control in a form as a parameter.  The date in the form can, therefore, be in the regional date format set for the system.  Declaring the parameter will also ensure that the parameter is evaluated correctly on the basis of the system's regional date format.

    Ken Sheridan, Stafford, England




    • Edited by Ken Sheridan Wednesday, March 30, 2016 12:51 PM Typo corrected.
    • Marked as answer by David_JunFeng Thursday, April 7, 2016 9:12 AM
    Wednesday, March 30, 2016 12:47 PM

All replies

  • How do you access the SQL Server backend?

    When you're using linked tables or using the date in an Access query, then you need the US-date format: #mm/dd/yyyy# without quotes. See also Wrong Formatting in Code.

    When you're working server-side, then you need a SQL Server date literal. Use 'yyyymmdd' with single quotes.

    • Marked as answer by David_JunFeng Thursday, April 7, 2016 9:12 AM
    Wednesday, March 30, 2016 11:53 AM
  • Is the parameter declared as DATETIME?  If not it might well be misinterpreted as an arithmetic expression.

    Stefan is quite right about date literals, as entering a date literal in UK format would change 4th July to 7th April.  I always use the ISO standard for date notation of YYYY-MM-DD.  In this case, however, you are referencing a control in a form as a parameter.  The date in the form can, therefore, be in the regional date format set for the system.  Declaring the parameter will also ensure that the parameter is evaluated correctly on the basis of the system's regional date format.

    Ken Sheridan, Stafford, England




    • Edited by Ken Sheridan Wednesday, March 30, 2016 12:51 PM Typo corrected.
    • Marked as answer by David_JunFeng Thursday, April 7, 2016 9:12 AM
    Wednesday, March 30, 2016 12:47 PM