Link to home
Start Free TrialLog in
Avatar of asd987
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
ASKER CERTIFIED SOLUTION
Avatar of SpyMaster
SpyMaster

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of asd987
asd987

ASKER

Thanks - I thought it's be something pretty easy like that !