Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2009
    Posts
    4

    Unanswered: Access 2010 Reports - Dates in SQL

    I am having trouble figuring out what is going wrong with some SQL in my reports.

    The reports on my database have variable start and end dates, but when I pass the SQL generated into a report for printing, the data reported on does not reflect the data that would be collected by the SQL as is.

    I am using a routine (SQLDate) to convert the dates from European to US date format -

    Function SQLDate(vDate As Variant) As String
    If IsDate(vDate) Then
    SQLDate = "#" & Format$(vDate, "mm\/dd\/yyyy") & "#"
    End If
    End Function

    The SQL generated is as follows:


    SELECT policyNo, PolicyNm, NextReviewDt, ApprovDt, Reviewer, Comments, Status
    FROM Policy
    WHERE approvdt <= DateValue(#01/25/2009#)
    ORDER BY ApprovDt;

    This is not picking up the data I am expecting by inspecting the table.

  2. #2
    Join Date
    Dec 2009
    Posts
    4
    I have a bit of extra information. I ran one of my reports feeding in the dates 1/1/2012 and 31/12/2012. These are correctly formatted into US date for the SQL, but when I look at the query after it has been applied, the dates are in 2011 not 2012. I get the same result for some reason when running for the dates 1st Jan to 31st Dec 2010 - the year in the query afterward is showing as 2011.

Posting Permissions

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