CODEWITHSUNDEEP
vbams-access
AdorableVB
AdorableVB

Reputation: 1393

query for counting rows in sql with filters in ms access vba

I am working on a simple read write database with design form. I used this query for counting all rows SELECT COUNT (*) FROM tblData

Now I can't get to count the rows when I am adding a filter. I tried various queries, some instructions here, but I can't decode it and make it vba. I need to find all pending status of 338559

enter image description here

Dim db As Database
Dim rs As DAO.Recordset
Dim query As String
Dim i As Integer

query = "SELECT COUNT Status = 'Pending' FROM tblData WHERE UID = 338559"
Set db = CurrentDb
Set rs = db.OpenRecordset(query)

For i = 0 To recordCountOfTheItem
    'code to display it to listbox
Next i

Thank you for some assistance coders :)

Upvotes: 0

Views: 1457

Answers (4)

user6307642
user6307642

Reputation:

You can use that request :

SELECT COUNT(*)
FROM tblData
WHERE UID = 338559
  AND Status = 'Pending';

Upvotes: 4

Andre
Andre

Reputation: 27634

For the record: To fill a listbox with data from a query, you need neither Count() nor VBA at all.

Just set the listbox RowSource to the query, e.g.

SELECT <whichever fields you want to display> FROM tblData
WHERE UID = '338559' AND Status = 'Pending';

Upvotes: 0

Gustav
Gustav

Reputation: 55806

You don't have to count, just loop:

query = "SELECT COUNT Status = 'Pending' FROM tblData WHERE UID = 338559"
Set db = CurrentDb
Set rs = db.OpenRecordset(query)

If rs.RecordCount > 0 Then
    While rs.EOF = False
        'code to display it to listbox
        rs.MoveNext
    Wend
End If
rs.Close

Set rs = Nothing
Set db = Nothing

Upvotes: 0

Arulkumar
Arulkumar

Reputation: 13237

You can achieve this by CASE statement, if the condition is satisfies set as 1 and if not satisfies set as NULL. This will work for MySQL

When Count it won't consider the null values, so you will get valid count for your filter

SELECT COUNT(CASE WHEN Status = 'Pending' THEN 1 ELSE NULL END) 
FROM tblData WHERE UID = 338559

For VBA Access 2007

SELECT COUNT(IIF(Status = 'Pending', 1, NULL))
FROM tblData WHERE UID = 338559

Upvotes: 0

Related Questions