Count or Dcount 1

[Domino2]

I have a listbox that shows records in three states by virtue of an integer field named "Done" Three buttons select how the list should be filled, Done=1, Done=0 or all records. That part all works okay, however I have three textboxes that are to show the number of records in all states of the Done value, again =1, =0, or all. How can I get a count value for each? My SQL for the listbox states are:

Mysql = "SELECT TDOCS.DOCID, TDOCS.MDIR, TDOCS.Document, TDOCS.Done FROM TDOCS"
Mysql = Mysql & " ORDER BY TDOCS.Document;"
Me.SHOWDOCS.RowSource = Mysql

Mysql = "SELECT TDOCS.DOCID, TDOCS.MDIR, TDOCS.Document, TDOCS.Done FROM TDOCS"
Mysql = Mysql & " WHERE (((TDOCS.Done) = 1))"
Mysql = Mysql & " ORDER BY TDOCS.Document;"
Me.SHOWDOCS.RowSource = Mysql

Mysql = "SELECT TDOCS.DOCID, TDOCS.MDIR, TDOCS.Document, TDOCS.Done FROM TDOCS"
Mysql = Mysql & " WHERE (((TDOCS.Done) = 0))"
Mysql = Mysql & " ORDER BY TDOCS.Document;"
Me.SHOWDOCS.RowSource = Mysql

Many thanks

 

[MajP]

There are a lot of ways to do this. One way

Public Function getCount() As Integer
  getCount = Me.lstOne.ListCount
End Function

set the control source of your text box to

=getCount

make sure to requery the text box to refresh the function everytime you cange the rowsource of the listbox

 

[Domino2]

Many thanks. However I am trying to get the three values before any listbox gets filled. I could use the listcount of the list when its filled, but I need to show the user the totals seperately. Thanks

 

[PHV]

One way:
Me![TextBoxAll] = DCount("*", "TDOCS")
Me![TextBoxDone0] = DCount("*", "TDOCS", "Done=0")
Me![TextBoxDone1] = DCount("*", "TDOCS", "Done=1")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Domino2]

PERFECT many thanks PHV