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") & "#"
The SQL generated is as follows:
SELECT policyNo, PolicyNm, NextReviewDt, ApprovDt, Reviewer, Comments, Status
WHERE approvdt <= DateValue(#01/25/2009#)
ORDER BY ApprovDt;
This is not picking up the data I am expecting by inspecting the table.
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.