VBA date parameter strangeness

[Colin Sewell]

This code used to work, but I suspect it may have been broken by the
SQL server SP2 update.

I have an Access database of data where one of the fields is the
date/time the data was inserted. I am trying to create a recordset of
data falling between two dates. I have an SQL SELECT statement with
BETWEEN parameters. I create a command object for this statment and
add the lower and upper date range parameters and use it with a
recordset Open. It returns 0 records, but it shouldn't. If I change
the date parameters slightly, the data is returned. Here is some
sample code. This *doesn't* work; findrs.RecordCount returns 0:



code:--------------------------------------------------------------------------------
Sub Test()
Dim findcmd As New ADODB.Command
Dim findpar1 As ADODB.Parameter
Dim findpar2 As ADODB.Parameter
Dim findrs As New ADODB.Recordset
Dim SQLTIMESEL As String
Dim DateLower As Date
Dim DateUpper As Date

SQLTIMESEL = " WHERE (RTCallTime BETWEEN ? AND ?)"

DateLower = "1-apr-2005 00:00:00"
DateUpper = "2-apr-2005 23:59:59"

Set findpar1 = findcmd.CreateParameter("@DateLower",
adDBTimeStamp, adParamInput, , DateLower)
findcmd.Parameters.Append findpar1

Set findpar2 = findcmd.CreateParameter("@DateUpper",
adDBTimeStamp, adParamInput, , DateUpper)
findcmd.Parameters.Append findpar2

findcmd.CommandText = "SELECT * FROM stats " + SQLTIMESEL + "
ORDER BY RTCALLTIME"
findcmd.ActiveConnection = Application.CurrentProject.Connection

findrs.Open findcmd, , adOpenKeyset, adLockOptimistic

If findrs.RecordCount = 0 Then
findrs.Close
Set findrs = Nothing
Set findcmd = Nothing
Exit Sub
End If

findrs.MoveFirst

Do
Debug.Print findrs!RTCallTime,
findrs.MoveNext
Loop Until findrs.EOF

End Sub
--------------------------------------------------------------------------------


However, if I change findpar1 and findpar2 to:

code:--------------------------------------------------------------------------------
Set findpar1 = findcmd.CreateParameter("@DateLower",
adDBTimeStamp, adParamInput, , Format(DateLower, "mm/dd/yyyy
hh:mm:ss"))
Set findpar2 = findcmd.CreateParameter("@DateUpper",
adDBTimeStamp, adParamInput, , Format(DateUpper, "mm/dd/yyyy
hh:mm:ss"))
--------------------------------------------------------------------------------


The debugger shows the original findpar.Value's as what I set them to,
the modified ones show dates of January 4 and February 4 because the
day and month are switched. Yet it finds the data. I've checked the
date/time values stored as real numbers and they appear to be correct.

My regional settings has been changed to show dates in dd-mmm-yyyy
format. But the date parameters will only be accepted in US format?

What mistake have I made?

 

[MacDermott]

AFAIK, query parameters must always be in US format, no matter what your
PC's regional settings are.

 

[Colin Sewell]

AFAIK, query parameters must always be in US format, no matter what your
PC's regional settings are.

Yeah, but I'm passing binary dates as the parameters, not something
that has to be decoded from a string. In any case, I found my
problem. I should have been using adDate as the parameter type, not
adDBTimeStamp. Once I changed that, it started working as expected
again.