Reputation: 21
I have a access table that I am doing a search by date range on. In the form I have a text box TxtTotal that I want to display the number of records in the filtered range the code I have. keeps giving me the complete number of records and not the range filtered.
This is my module
Function FindRecordCount(strSQL As String) As Long
Dim db As Database
Dim rstRecords As Recordset
'On error GoTo ErrorHandler
Set db = CurrentDb
Set rstRecords = db.OpenRecordset("TblPurchases")
If rstRecords.EOF Then
FindRecordCount = 0
Else
rstRecords.MoveLast
FindRecordCount = rstRecords.RecordCount
End If
rstRecords.Close
db.Close
Set rstRecords = Nothing
Set db = Nothing
End Function
This is my code for the TxtTotal text box on the form
Sub Search()
Dim strCriteria, task As String
Me.Refresh
If IsNull(Me.TxtPurchaseDateFrom) Or IsNull(Me.TxtPurchaseDateTo)
Then
MsgBox "Please enter the date range", vbInformation, "Date Range
Required"
Me.TxtPurchaseDateFrom.SetFocus
Else
strCriteria = "([Date of Purchase] >= #" & Me.TxtPurchaseDateFrom &
"# and [Date of Purchase] <= #" & Me.TxtPurchaseDateTo & "#)"
task = "select * from TblPurchases where( " & strCriteria & ") order
by [Date of Purchase] "
DoCmd.ApplyFilter task
Me.TxtTotal = FindRecordCount(task)
End If
End Sub
the results keeps giving me the complete number of records and not the range filtered.
Upvotes: 1
Views: 2783
Reputation: 56026
You could replace all this with a DCount expression in the ControlSource of the textbox txtTotal
:
=DCount("*","TblPurchase ","[Date of Purchase] Between #" & Format(Nz(Me!TxtPurchaseDateFrom.Value,Date()), "yyyy\/mm\/dd") & "# And #" & Format(Nz(Me!TxtPurchaseDateTo.Value,Date()), "yyyy\/mm\/dd") & "#")
Upvotes: 0
Reputation: 138
I believe the main issue is this line:
Set rstRecords = db.OpenRecordset("TblPurchases")
You are setting the Record set to use the table as its source instead of your SQL string. No matter what your input dates are, if you are looking at the whole table, it will return the whole table xD.
As for finding the total count of items from a query result, it might make sense to use the SQL COUNT function eg: SELECT COUNT(<Column>) FROM <table> WHERE <criteria>;
This will provide you the number of data entries that are provided from that query.
I would also recommend using the QueryDef Object for your SQL definitions since it makes things a little cleaner. But again, this is just a recommendation EG:
Function FindRecordCount(dateFrom As Date, dateTo As Date) As Long
Dim db As DAO.Database
Dim QDF As DAO.QueryDef
Dim rstRecords As DAO.Recordset
Dim SQL As String
SQL = "SELECT COUNT(*) FROM TblPurchase WHERE([Date of Purchase] >= #@dateFrom# AND [Date of Purchase] <= #@dateTo#)"
Set db = CurrentDb
Set QDF = db.QuerDefs(SQL)
QDF.Paramaters("@dateFrom").Value = dateFrom
QDF.Paramaters("@dateTo").Value = dateTo
Set rstRecords = QDF.OpenRecordset("TblPurchases")
If rstRecords.EOF Then
FindRecordCount = 0
Else
rstRecords.MoveLast
FindRecordCount = rstRecords.RecordCount
End If
rstRecords.Close
QDF.Close
db.Close
Set rstRecords = Nothing
Set QDF = Nothing
Set db = Nothing
End Function
Best Regards.
Upvotes: 0