Count items in listbox with specific text (1 Viewer)

Hi,

I am trying to count the number of rows in my listbox where value is "Completed"

For example, I have a listbox with 2 columns.

Job Name | Job Status

The name of listbox is SearchResults

For each row, the value of job status can be Pending or Completed

I want to be able to count the number of jobs that has status of "Completed"

I tried using this formula as a rowsource of my textbox

but I get #error as a result.

I double check if I am referencing the right object by counting everything using this formula

and it return the total number of rows in the listbox.

Anyone knows how to count specific row in a listbox based on it's text/value?

Thanks

You cant count a listbox, well you can perhaps if you loop it.... However in most cases re-doing the select statement sourcing the listbox but adapting it to suite your (new) needs is easiest.

you should make a public function and put it in a module.
on your textbox controlsource:
=fnCountCompletedInList([yourListboxName])

public function fnCountCompletedInList(byval lst As Access.ListBox) As Integer

dim i as integer
dim iCount as integer
for i = 0 to lst.ListCount - 1
if lst.column(1, i) = "Completed" then iCount = iCount + 1
next
fnCountCompletedInList=iCount
End Function

arnelgp said:

you should make a public function and put it in a module.
on your textbox controlsource:
=fnCountCompletedInList([yourListboxName])

public function fnCountCompletedInList(byval lst As Access.ListBox) As Integer

dim i as integer
dim iCount as integer
for i = 0 to lst.ListCount - 1
if lst.column(1, i) = "Completed" then iCount = iCount + 1
next
fnCountCompletedInList=iCount
End Function

Click to expand...

WOW!

Works like a charm!