Date & ADO **RESOLVED**

[GlenW]

Why does this not work?
With Debug firstDate & lastDate are correct but the SQL statement does not return any values, and I know they are there.
Please help.

Dim firstDate As Date
        Dim lastDate As Date
        
        myChart.Row = 1
        firstDate = myChart.RowLabel
        
        myChart.Row = myChart.RowCount
        lastDate = myChart.RowLabel
        
        'Get and print history
        cnnCon.Open dataText
        rsRes.Open "SELECT Date, Result, SampleComments.Comment " & _
                   "FROM SampleResults, SampleComments " & _
                   "WHERE SampleResults.ID = SampleComments.ID " & _
                   "AND SampleResults.TestName = " & Chr(34) & _
                   cboTest.Text & Chr(34) & _
                   " AND Date >= " & firstDate & _
                   " AND Date <= " & lastDate & _
                   " ORDER BY Date", _
                   cnnCon, _
                   adOpenKeyset, _
                   adLockReadOnly, _
                   adCmdText
                   
        With rsRes
            If (Not (.BOF And .EOF)) Then
                .MoveFirst
                While (Not .EOF)
                    Printer.Print "Date: " & .Fields("Date").Value & _
                                  "  Result: " & .Fields("Result").Value & vbTab & _
                                  "  Comment: " & .Fields("Comment").Value
                    .MoveNext
                Wend
            End If
        End With
        
        rsRes.Close
        cnnCon.Close

[Joacim Andersson]

Try enclosing the dates withing # characters:

" AND Date >= #" & firstDate & _
"# AND Date <= #" & lastDate & "#" '...the rest

Best regards

[Si_the_geek]

you need quotes or hashes around the dates, eg:

" AND Date >= '" & firstDate & _
                   "' AND Date <= '" & lastDate & _
                   "' ORDER BY Date", _

or:

" AND Date >= #" & firstDate & _
                   "# AND Date <= #" & lastDate & _
                   "# ORDER BY Date", _

[GlenW]

Nope, still doesn't work.
Could it be confusing UK and US date formats?
If yes how do I fix it?

[Si_the_geek]

yep, common problem...

" AND Date >= #" & format(firstDate,"mm/dd/yyyy") & _
                   "# AND Date <= #" & format(lastDate,"mm/dd/yyyy") & _
                   "# ORDER BY Date", _

[GlenW]

You are stars, Thanks a lot
Works perfectly.

[darre1]

you could use the Int function

i.e.

WHERE Int(Date) <= int(Date2)

etc, that would work.

NOTE: The int function will chop off the decimals, thus chopping off the time part of the dates when comparing

[GlenW]

Originally posted by darre1
you could use the Int function

i.e.

WHERE Int(Date) <= int(Date2)

etc, that would work.

NOTE: The int function will chop off the decimals, thus chopping off the time part of the dates when comparing

What a good idea!!

[darre1]

Originally posted by GlenW

What a good idea!!

thanks!

note that if you want to catch a date between two dates though you should add 1 to the end date, else it will only catch between 00:00:01 of the second date if you get my drift.

i.e.

SELECT * FROM table WHERE Int(dtmDate) BETWEEN Int(Date1) AND Int(Date2) + 1; 
'basically from 00:00:00 on date1 to 23:59:59 on date2
'if you leave the +1 out it won't include the full day for date 2!