cannot query sql server table with smalldatetime type

[uoghk]

I have a Invoice table in SQL server. It has a TxDate field in SmallDateTime type.



I get error : type mismatch in below code.

Set Rst = Db.OpenRecordset("SELECT * FROM Invoice WHERE TxDate='" & Format(Me.TxDate,'yyyy-mm-dd') & "'", dbOpenDynaset, dbSeeChanges)

If I change the condition as below

Set Rst = Db.OpenRecordset("SELECT * FROM Invoice WHERE CAST(TxDate AS CHAR(10))='" & Format(Me.TxDate,'yyyy-mm-dd') & "'", dbOpenDynaset, dbSeeChanges)

Then I get error : missing operator in query expression.

[Gicu]

The Format function returns a string so I think that is the root of your problem. And you cannot use CAST in Access, you would use CStr to convert to string. If your TxDate in SQL includes minutes and you want to ignore those you should use the DateValue() function:

Set Rst = Db.OpenRecordset("SELECT * FROM Invoice WHERE DateValue(TxDate)=#" & CDate(Me.TxDate) "#", dbOpenDynaset, dbSeeChanges)

or even:

Set Rst = Db.OpenRecordset("SELECT * FROM Invoice WHERE DateValue(TxDate)=#" & Me.TxDate "#", dbOpenDynaset, dbSeeChanges)

Cheers,

[uoghk]

Thanks Gicu.