incorrect returned dates with SQL BETWEEN with MS ACCESS 2000

sql between dates returns incorrect dates

[xemxland]

Hi Guys,

I'm all burnt out with ideas, and hope someone out there can help. I have spent hours of time looking in forums for a solution but nothing works.

I have a BETWEEN sql statement which returns dates between a certain range. Whilst I get results, they are not correct:

For example to return all dates between 01/10/2007 and 31/10/2007, i get the following results:

13/09/2007
10/06/2007
09/09/2007
02/04/2007
09/09/2007
20/09/2007

Despite using many different varieties of SQL statements including enclosing the dates in #, formatting the dates to the correct format, doube checking the date/time of the access database itself (ddate short) and even attempted an operator approach (i.e. >= date and <= date) , i get the same incorrect results... I really need some help on this as I am struggling to understand why this is happening.

Here is the current code:

Note - q = "01/10/2007&31/10/2007" - i am also using the code with AJAX to provide seamless changing of dates without having to reload the main container page. The ajax portion is working fine, just the incorrect results pulled from the ASP page... Thanks in advance guys!

<%
response.expires=-1
q=request.querystring("q")
Dim objConn, objID
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=C:\Domains\dolphin-yachts.com\db\boats.mdb"
set objID = server.createobject("adodb.recordset")
objID.CursorLocation = 3
strQuery = "SELECT Ref,FirstName,LastName,Company,ImportantDate1,IDate1Why,ImportantDate2,IDate2Why FROM Contacts"
if len(q)<>1 then
iPos = InStr(q, "&")
d1 = Left(q, iPos - 1)
d2 = Mid(q, iPos + 1)
strQuery = strQuery & " WHERE"
strQuery = strQuery & " (ImportantDate1 between format('"&d1&"', 'mm/dd/yyyy') AND format('"&d2&"', 'mm/dd/yyyy'))"
'strQuery = strQuery & " ((ImportantDate1 >= #" & DateValue(d1) & "#) AND (ImportantDate1 <= #" & DateValue(d2) & "#))"
'strQuery = strQuery & " OR ((ImportantDate2 >= #" & DateValue(d1) & "#) AND (ImportantDate2 <= #" & DateValue(d2) & "#))"
end if
objID.Open strQuery,objConn,3,3
hint=""
if len(q)<>1 then
hint=hint & "<p style=""margin-left:14;"">Important dates between " & d1 & " and " & d2 & "</p>"
end if
hint=hint & "<table cellpadding=""0"" cellspacing=""0"" width=""100%"" border=""0"" style=""margin-left:14;"">"
Do While Not objID.EOF
if not ( isnull(objID("ImportantDate1")) OR objID("ImportantDate1")="" ) then
hint=hint & "<tr><td>" & objID("ImportantDate1") & "</td><td><p>" & objID("IDate1Why") & "</p></td><td><a href=""viewContact.asp?id=" & objID("Ref") & """>" & objID("Ref") & "</a></td>"
hint=hint & "<td>" & objID("FirstName") & " " & objID("LastName") & "</td>"
hint=hint & "</tr>"
end if
if not ( isnull(objID("ImportantDate2")) OR objID("ImportantDate2")="" ) then
hint=hint & "<tr><td>" & objID("ImportantDate2") & "</td><td><p>" & objID("IDate2Why") & "</p></td><td><a href=""viewContact.asp?id=" & objID("Ref") & """>" & objID("Ref") & "</a></td>"
hint=hint & "<td>" & objID("FirstName") & " " & objID("LastName") & "</td>"
hint=hint & "</tr>"
end if
objID.MoveNext
Loop
objID.Close
Set objID = Nothing
objConn.Close
Set objConn = Nothing
hint=hint & "</table>"
if hint="" then
 response.write("error")
else
 response.write(hint)
end if
%>

[syber]

I believe that MS Access dates are US centric.

Try BETWEEN '10/01/2007' AND '10/31/2007'

[xemxland]

Hi Thanks for the quick response,

Unfortunately formatting the dates doesnt seem to work and returns the same wrong dates. Granted tht the format mm/dd/yyyy or mm-dd-yyyy are the only formats i tried that return any results but they are still incorrect not between the date range i specify...

Heres the 2 i tried:

strQuery = strQuery & " (ImportantDate1 between format('"&d1&"', 'mm/dd/yyyy') AND format('"&d2&"', 'mm/dd/yyyy'))"
strQuery = strQuery & " (ImportantDate1 between format('"&d1&"', 'mm-dd-yyyy') AND format('"&d2&"', 'mm-dd-yyyy'))"

Any other ideas?

[LifeinAsia]

My guess is that Access thinks you're using a character string for comparison instead of a date object in the query. Trying outputting strQuery to see exactly what is being passed.

[xemxland]

Hi Tahnks for that.

Heres the outputed strQuery:

SELECT Ref,FirstName,LastName,Company,ImportantDate1,IDate1Why,ImportantDate2,IDate2Why FROM Contacts WHERE (ImportantDate1 between format('01/10/2007', 'mm/dd/yyyy') AND format('31/10/2007', 'mm/dd/yyyy')) OR (ImportantDate2 between format('01/10/2007', 'mm/dd/yyyy') AND format('31/10/2007', 'mm/dd/yyyy'))

I've not worked with Dates in this way and cant see any obvious problem... maybe someone out there can help me out.

I do't know if it would help at all, but previously the access database was written in 97, which has been upsized to 2000. In Access 97, the date/time fields where set for a format of dd-mm-yyyy, but since the upsize I have amended it to a format of ShortDate.

Any ideas :S its quite urgent ... aaahhhh!

[LifeinAsia]

My guess is that the format fuction is forcing a character string instead of a datetime value. Try just doing it directly as "between '01/10/2007' AND '31/10/2007'" (and you may want to try changing the format to mm/dd/yyyy instead of dd/mm/yyyy).

[xemxland]

ok, so i tried:

strQuery = strQuery & " (ImportantDate1 between '"&d1&"' AND '"&d2&"')"
strQuery = strQuery & " OR (ImportantDate2 between '"&d1&"' AND '"&d2&"')"

but that doesnt seem to work (data type mismatch) with single quotes so i tried

strQuery = strQuery & " (ImportantDate1 between #"&d1&"# AND #"&d2&"#)"
strQuery = strQuery & " OR (ImportantDate2 between #"&d1&"# AND #"&d2&"#)"

which returns results, but again the same as before, incorrect results... not in the range they sould be

i have also tried the dates direct with the same incorrect results

strQuery = strQuery & " (ImportantDate1 between #01/10/2007# AND #31/10/2007#)"
strQuery = strQuery & " OR (ImportantDate2 between #01/10/2007# AND #31/10/2007#)"

which ever combination i use that returns results ith no errors always returns the wrong set of results that do not match the range indicated in the select statement.

:S

[LifeinAsia]

Try hard coding "between '10/1/2007' AND '10/31/2007'" (without the quotes) and what do you get? Also, what is the data type for the ImportantDat1 field in your DB?

[xemxland]

OK, so i tried what you said and it didnt quite work... However i tried this:

strQuery = strQuery & " (ImportantDate1 between #" & d1 & "# AND #" & d2 & "#)"
strQuery = strQuery & " OR (ImportantDate2 between #" & d1 & "# AND #" & d2 & "#)"

and it appears to be working.

Now all i have to do is somehow set the CalendarPopup() (standard calendar popup js script) to paste the correctly formatted date into the input boxes ... i.e. instead of currently dd/mm/yyyy, i need it to do m/d/yyyy

if you got any tps for this, i'd greatly appreciate it... this has been a long one, and i'll be happy to get it over with... :s

[LifeinAsia]

You'll either have to parse out the data from d1 & d2 or tweak the code in CalendarPopup to give you the specific date components that you can rearrange in the order you want. I haven't used CalendarPopup so I can't help you there.

[xemxland]

Thats fine, It was fairly simple once took a good look at the code after being overwhelmed by this date issue.

So i got the datepicker working and all seems to be working great.

I need just one more thing, which i've not done before.
As you know there are two dates ImportantDate1 and ImportantDate2, both fields within the Access Database holding dates. I want to be able to sort by the dates after total order as opposed to

ORDER BY ImportantDate1,ImportantDate2 DESC

Which will simply order the dates held in Date1 then in Date2

What i need to do is somehow merge both date fields into a temporary column using an AS i suppose (not used AS before) and then sort that column created on the fly.

Hope i explained it good enough.

Any ideas?

[LifeinAsia]

I'm not sure what the difference would be between the ORDER BY part you listed or concatenating the date strings then ordering. Can you give some example data and the results you woudl like to see?

[xemxland]

ok heres the same data

Column ImportantDate1:
16/10/2007
01/08/2007
10/11/2007

Column ImportantDate2:
06/09/2007
15/10/2007

if I do a order by Column1,Column2

ORDER BY ImportantDate1,ImportantDate2 DESC

i would expect the results as follows:

10/11/2007 - column 1
16/10/2007 - column 1
01/08/2007 - column 1
15/10/2007 - column 2
06/09/2007 - column 2

But what I would like to do is concatenate ImportantDate1 and ImportantDate2 for the purposes of SORTING (still keeping the 2 columns seperate) so that the results returned are as follows:

10/11/2007 - column 1
16/10/2007 - column 1
15/10/2007 - column 2
06/09/2007 - column 2
01/08/2007 - column 1

Hope that helps you get a better idea of what im trying to do.

[LifeinAsia]

Um, no... You're going to have ImportantDate1 and ImportantDate2 appear in the same row (each row will contain the following 8 fields, according to your original posting: Ref, FirstName, LastName, Company, ImportantDate1, IDate1Why, ImportantDate2, IDate2Why).

[xemxland]

Hi, actualy no,

each row has 4 fields as the if...then statements pull apart importantdate1 and importantdate 2 into there own respecitive rows...

but im guessing what i want to do is not possible :S

Would a GROUP BY HAVING work in anyway to at least distinctively seperate the importantdates1 and importantdates2, i.e.

Important Dates 1
DATE
DATE
DATE
DATE

Important Dates 2
DATE
DATE

for example

[LifeinAsia]

But the ifs are done after the query has finished and already returned the data. With seeing the specific data, I might be able to suggest something, but it seems like you're trying to do soemthing that SQL wasn't designed to do.