Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

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
0
asd987
Asked:
asd987
1 Solution
 
SpyMasterCommented:
I cannot think of any other better way then this below.

Dim lMyDB As Database, lMYRs As Recordset
Set lMyDB = DBEngine.OpenDatabase("c:\ecommerce\eshop.mdb")
Set lMYRs = lMyDB.OpenRecordset("Select * from cart where cookieid=" & cookieid$)
If lMYRs.RecordCount > 0 Then lMYRs.MoveLast
MsgBox lMYRs.RecordCount & " records have been found."
0
 
asd987Author Commented:
Thanks - I thought it's be something pretty easy like that !

0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now