dates in SQL string

[muratas2]

Hi,

I've got below piece of code;

Dim cr,rd
Dim strSQL As String
Dim rsa As New ADODB.Recordset
Dim cnn As Connection
Set cnn = CurrentProject.Connection

cr = txtCur
rd = txtRateDate

strSQL = "select " & cr & " from tblRates where ExchDate=" & "#" & rd & "#"

rsa.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

As seen, rd contains a date. When I try to open a recordset with this SQL string, this date value is not selected. I don't receive any error, but the recordset is not filtered depending on the variable rd's value so, rsa.RecordCount=0. Where am I wrong? How can I pass a date variable in where criteria?

Thanks a lot

Murat

 

[Pat Hartman]

Try dimming rd as a date rather than a variant. You'll probably have to trap for errors in case txtRateDate does not contain a date in the proper format.

 

[BarkerD]

I had this problem as well. I looked in the help files, and finally found something that was useful.

I think your problem is with how you are adding your # sign.

strSQL= " SELECT " & cr & " FROM tblrates WHERE ExchDate = #" & rd & "#;"