Filtering a record set based on a date

[CES]

All,
I'm having a devil of a time trying to figure out how to filter a record set based on a date. I've tried a few different variations of the following lines, but unfortunately, none seem to work.


.Filter = .Fields("tTransactionDate") >= DateAdd("d", -90, Date)
.Filter = DateValue(.Fields("tTransactionDate")) >= DateAdd("d", -90, Now())

If anyone can provide me with some advice or guidance on how to accomplish the above it would be much appreciated. Thanks in advance. - CES


Public Function Test(tmp As String) As String

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strToReturn As String
Dim x As String

strSQL = "SELECT DatePart('yyyy',[tTransactionDate]) AS [Year],[Transaction].[tTransactionDate],[Transaction].[tTransactionType],[Transaction].[tTransactionFees],[Transaction].[tTransactionCredits], [comboTransactionType].[TransactionSort],[comboTransactionType].[TrasactionType] FROM comboTransactionType RIGHT JOIN [Transaction] ON [comboTransactionType].[ID] = [Transaction].[tTransactionType] WHERE (((DatePart('yyyy',[tTransactionDate]))=DatePart('yyyy',Now()))) ORDER BY
Transaction.tTransactionDate;"

Set rst = New ADODB.Recordset


With rst
.ActiveConnection = CurrentProject.Connection
.Open Source:=strSQL, CursorType:=adOpenKeyset, Options:=adCmdText

If .EOF Then
strToReturn = 0
Else

.Filter = ???

x = .RecordCount


strToReturn = x
End If

End With

rst.Close
Set rst = Nothing

Test = strToReturn
End Function

 

[Ken Snell \(MVP\)]

The Filter property is a text string that represents the actual filter; for
example:

..Filter = "FieldName Between #1/1/2006# And #11/18/2006#

So you'd need something like this:

..Filter = "tTransactionDate >= #" & Format(DateAdd("d", -90,
Date),"m/d/yyyy") & "#"

 

[Ken Snell \(MVP\)]

Thanks, Jamie.

--

Ken Snell
<MS ACCESS MVP>

The ADO Recordset's Filter property does not use the target DBMS's SQL
syntax; rather the Filter must comply it's own syntax which does not
include a BETWEEN keyword, so your Filter would have to be written
using >= and <= predicates e.g.

.Filter = "FieldName >= #1/1/2006# AND FieldName < #11/19/2006#"

See:
ADO 2.8 API Reference: Filter Property
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprofilter.asp

Jamie.