Counting records generated by selecting an option from a listbox

[SGT Beene]

I have a database that requires a user to select an option from a listbox to
complete a form. The data from the form goes to a table. I need to be able
to count the number of records associated with each particular option from
the listbox at the bottom of the form. I have tried using the expression
builder for the data source with a couple of different formulas. I have
tried:

=Count([TableName]![FieldName]=’Value’)

As well as:
=Sum([TableName]![FieldName]=’Value’)

And:
=-Sum([TableName]![FieldName]=’Value’)

We tried creating a query that pulls only the records with the value
desired, and that worked to the point that if there were X number of records
on the table with the same value, the query returned X number records. We
tried to pull the information from the query and had some limited success
with the following formula:


In the data source field:
=FieldName

And at the same time in the default value:
=Count([QueryName]![FieldName]=’Value’)

However this returned a count of all records on the table instead of those
on the query. This database is used to create various reports for our unit
and it needs to be pretty much idiot proof because we never know who is going
to have to get into it to print out those reports. Any help would be greatly
appreciated.

 

[Nikos Yannacopoulos]

What you need is the domain aggregate function DCount() -look it up in
help. To give you an example, assuming you need to count records in
table MyTable, where the value of field MyField in the table is equal to
the selected value in listbox MyListBox on form MyForm, the syntax would be:

DCount("*","MyTable","[MyField]=" & Forms![MyForm]![MyListBox])

if MyField is numeric, or

DCount("*","MyTable","[MyField]='" & Forms![MyForm]![MyListBox] & "'")

if MyField is text.

HTH,
Nikos