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

Question

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

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 :)

If your actual goal is to fill a listbox from a query (filtered table), then you don't need the count at all. Is this the case? (see: meta.stackexchange.com/questions/66377/what-is-the-xy-problem) — Andre
– Andre, Commented May 20, 2016 at 12:54
yep, the goal is to fill that listbox with the filtered table. should I ask another for that? Well the solution would be to loop through the filtered table so I need the count - in which case the query is not working, so that's what I asked here. But good point on that link :D I probably just added the whole problem so that you can see where Im coming from.. — AdorableVB
– AdorableVB, Commented May 21, 2016 at 9:13

score 4 · Accepted Answer · 2016-05-24 21:53:25Z

4

You can use that request :

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

edited May 24, 2016 at 21:53

answered May 20, 2016 at 11:39

user6307642

Sign up to request clarification or add additional context in comments.

1 Comment

AdorableVB Over a year ago

this is the right query, though it was solved by @arulkumar by reminding me of what type the UID is, I have put it to text that's why the where clause doesn't work.

Arulkumar · Accepted Answer · 2016-05-20 11:51:12Z

0

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

edited May 20, 2016 at 11:51

answered May 20, 2016 at 11:45

Arulkumar

13.3k14 gold badges51 silver badges76 bronze badges

8 Comments

AdorableVB Over a year ago

Hi, thanks for the input, but is this sql accepted for vba access 2007? I get syntax error (missing operator) in that query. Also ebahi's answer, gets me data mismatch/syntax error too.

AdorableVB Over a year ago

run time error 3464 - data type mismatch in criteria expression. I also tried this one select count (*) from tblData where UID = 338559 same error. But when I dont add the where clause it does push through

Arulkumar Over a year ago

What is the data type of UID? Is INT or VARCHAR ?

AdorableVB Over a year ago

I don't know where to check, I think that is only for mySQL format database. I only use access, and the UID field is set to short text/text, there's no indication if its int or varchar, but I assume its varchar.

Arulkumar Over a year ago

@AdorableVB : Then you can use the WHERE clause as WHERE UID = '338559'

|

Gustav · Accepted Answer · 2016-05-20 13:20:51Z

0

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

answered May 20, 2016 at 13:20

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Comments

Andre · Accepted Answer · 2016-05-21 11:34:54Z

0

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';

answered May 21, 2016 at 11:34

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

8 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related