count specific records from a field in report footer

[Guest]

i have a field containing multiple values such as "sold", "inventory", etc.

i have created a text box in the report footer and i want it to count only the records containing the value "sold".

how can i achieve that?

your help is greatly appreciated!

 

[Jeff Boyce]

Angie

Storing more than one fact in one field (" i have a field containing
multiple values such as "sold", "inventory", etc.") violates one of the
tenants of relational database design. You will probably continue to have
headaches and heartburn getting Access to do what you consider should be
simple if you don't revisit your data structure and its normalization.

One way to count the number of rows containing the string "sold" in a field
would be to write a query using the "Like" operator in the criterion for
that field. Your criterion would look something like:

Like * & "sold" & *

and would return all rows with the string "sold" anywhere in that particular
field. Next, you can convert the query to a Totals query to get the count.

Or, consider using the DCount() function in your report to determine the
count, if your report doesn't contain detail rows for "sold". If it does,
you could use a textbox in the footer with a control source of something
like

=Count([YourRowIDFieldFromTheDetailSection])

 

[Guest]

Or you could try the following formula in your textbox:
=Count(IIF([YourFieldName] Like "sold",1,Null))

 

[Marshall Barton]

i have a field containing multiple values such as "sold", "inventory", etc.

i have created a text box in the report footer and i want it to count only the records containing the value "sold".

If by multiple values, you mean the field could have "sold"
or it could have "inventory", but not both, then you can use
any of these expressions in a text box:

=Count(IIf(thefield = "sold", 1, Null))
or
=Sum(IIf(thefield = "sold", 1, 0))
or
=Abs(Sum(thefield = "sold"))
or several other variations.

The above are listed in what I think is the order of
clarity, but reverse order of speed. Your call.