Access database - number of records which match select
Posted on 2000-05-01
I am writing a shopping cart and need to find out how many items in the table match the given criteria.
I can search through the table and get each record until there are no more matches and increment a counter using code like the code below - but there must be a better way - all I need to know is the number of records which match the select criteria.
Maybe there is some kind of select 'count' where cookieid='002' or something like that - if someone could let me know this I would be greatfull.
I could achive what I want using the following code but it's going to be pretty sloppy to have to step through each matching record when I probably don't need to.
Set lMyDB = DBEngine.OpenDatabase("c:\ecommerce\eshop.mdb")
Set lMYRs = lMyDB.OpenRecordset("cart", dbOpenDynaset)
lMYRs.FindFirst "cookieid=" + cookieid$
If lMYRs.NoMatch = False Then
' First match has been found
' Start a loop for counting additional matches
recordcount = recordcount + 1
lMYRs.FindNext "cookieid=" + cookieid$
Loop While lMYRs.NoMatch = False
' No items found
Set lMYRs = Nothing
Set lMyDB = Nothing