asd987
asked on
Access database - number of records which match select
Hi,
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
Do
recordcount = recordcount + 1
lMYRs.FindNext "cookieid=" + cookieid$
Loop While lMYRs.NoMatch = False
Else
' No items found
End If
lMYRs.Close
lMyDB.Close
Set lMYRs = Nothing
Set lMyDB = Nothing
Thanks
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:\
Set lMYRs = lMyDB.OpenRecordset("cart"
lMYRs.FindFirst "cookieid=" + cookieid$
If lMYRs.NoMatch = False Then
' First match has been found
' Start a loop for counting additional matches
Do
recordcount = recordcount + 1
lMYRs.FindNext "cookieid=" + cookieid$
Loop While lMYRs.NoMatch = False
Else
' No items found
End If
lMYRs.Close
lMyDB.Close
Set lMYRs = Nothing
Set lMyDB = Nothing
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER