When selecting a date from a DB, It never seems to work.

[Guest_imported]

When i am doing a select using ADO on a date field

eg: StockRes.Open "Select * from myTable where date = " & d & ", cn, adOpenKeyset, adLockReadOnly

StockREs.close

suppose d is a date in exactly the same format as the one in the DB. However the select never gets any records (i know there are records in the DB applying to the criteria)I have also tried hardcoding it.

Could it be a format problem?Ado problem ? Or is there some other unknown problem with date?

The field in the DB is a date/time field.

If i convert the field to text it works.However this is undesireable.

Can u please help me!!!!thanx

 

[NickISD]

Hi,
Try this - it works fine for ADO against Access or SQL server dates.Note date is wrapped by single quotes for SQL server, for Access replace single quote with #. If you always use the format below by using mmm then regardless of database date order it should still work. One final point, only use SELECT * if you have to, if you only need a couple of fields then name them in the select list.

Dim d as string,cSql as string
d=format(now,"dd/mmm/yyyy&quot

' ---- SQL Server
cSql = "Select * from myTable where date = '" & d & "'"
StockRes.Open cSql, cn, adOpenKeyset, adLockReadOnly

' ---- Access Database
cSql = "Select * from myTable where date = #" & d & "#"
StockRes.Open cSql, cn, adOpenKeyset, adLockReadOnly

Hope this helps

Regards, Nick