ADO Recordset Filter using date criteria causing an error

[jadedinvasion]

Okay so I'm having an issue with setting a filter on a ADO recordset using a field that has a date datatype.

Here how I set up my connection:

Dim objStatConn As ADODB.Connection
Dim objStatRS As ADODB.Recordset

Set objStatConn = New ADODB.Connection

objStatConn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=C:\working\records.mdb ;pwd=five"

objStatConn.Open

Set objStatRS = New ADODB.Recordset
objStatRS.CursorLocation = adUseClient

strSQL = "SELECT * FROM my RecordTable"
objStatRS.Open strSQL, objStatConn, adOpenForwardOnly
Set objStatRS.ActiveConnection = Nothing

objStatRS.Filter = "CRT_DTE = #7/26/2006#"

After putting a watch on the recordset, i see that before I apply the filter everything is groovy.

But once I set the filter, it gives me an error with the bookmark being "Either BOF or EOF is true, or the current record has been removed...." bla bla

BUT if i were to do something like this:

objStatRS.Filter = "ACT_ID = '1A2345S'"

It works just fine.

Am I missing something?

And Ideas would be most excellent!

 

[CautionMP]

jadedinvasion,
Does your field [tt]CRT_DTE[/tt] contain just a date or is it date and time?

If so you might try:

objStatRS.Filter = "DateValue(CRT_DTE) = #7/26/2006#"

Or

objStatRS.Filter = "Int(CRT_DTE) = #7/26/2006#"

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[jadedinvasion]

Thanks for your reply CMP,

The field is a date (date/time datatype in an MS Access database, but formated as short date)

When I tried the above code it gave me an error saying arguments are of the wrong type, are out of accpetable range or are in conflict with one another.

 

[CautionMP]

jadedinvasion,
I mislead you in my first post, apparently you can't apply a calculated filter to a ADODB recordset after it's open, sorry about that.

but formated as short date

The format will only affect how the data is displayed, not what is actually stored in the field. This may seem like splitting hairs until you try and filter the data. You could try this to account for any time values that may be included in your field:

objStatRS.Filter = "CRT_DTE >= #7/26/2006# AND CRT_DTE < #7/27/2006#"

Another method is to create a calculated field in your query that you could apply the filter to:

...
strSQL = "SELECT *[b], DateValue(CRT_DTE) AS FilterField[/b] FROM my RecordTable"
objStatRS.Open strSQL, objStatConn, adOpenForwardOnly
Set objStatRS.ActiveConnection = Nothing

objStatRS.Filter = "[b]FilterField[/b] = #7/26/2006#"
...

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[jadedinvasion]

GAH! It's Alive!!!!!!!

I remodified my sql statment with your suggestion above and it works like a charm.

Thanks so much for your help