Count in a Text Box Control Source Property

[Cro]

Hello Access Developers,

I'd like to know if it is possible to perform a count in an expression
that defines a control source.

My report is based on a query. In my report, I want a text box to
display the number of times a certain value appears in a certain field
(i.e. perform a ‘count'). I will be doing this for many values in many
fields so do not wish to have scores of queries to build my report.

I have tried setting the control source property of the text box to
several expressions (none of which have worked):

SELECT Count(*) AS Counted FROM tableData WHERE
(tableData.field1="foo");
=(SELECT Count(*) AS Counted FROM tableData WHERE
(tableData.field1="foo");)
= Count ( [tableData]![field1] = "foo" )

MS Access' help file tells me that I can set the control source
property to an expression, which is a combination of operators, field
names, functions, literals, and constants that evaluates to a single
value. It also says that expressions can specify criteria (such as
Order Amount>10000) or perform calculations on field values (such as
Price*Quantity).).

Question 1) Is there an expression that will count the number of times
a certain value appears in a certain field in the underlying query the
report is based on?

Question 2) If so, how would it then be possible to add these values
up in another text box? For example, text box ‘A' and text box ‘B'
count the number of times the value "a" and "b" appear in ‘field1' of
the underlying table ‘tableData' respectively. Could then a third text
box ‘C' sum the values calculated for text box A and text box B?

Thank you greatly for your wisdom and expertise.

Regards,

Cro

[John Winterbottom]

"Cro" <th*******@hotmail.com> wrote in message
news:24*************************@posting.google.co m...

Hello Access Developers,

I'd like to know if it is possible to perform a count in an expression
that defines a control source.

My report is based on a query. In my report, I want a text box to
display the number of times a certain value appears in a certain field
(i.e. perform a 'count'). I will be doing this for many values in many
fields so do not wish to have scores of queries to build my report.

I have tried setting the control source property of the text box to
several expressions (none of which have worked):

SELECT Count(*) AS Counted FROM tableData WHERE
(tableData.field1="foo");
=(SELECT Count(*) AS Counted FROM tableData WHERE
(tableData.field1="foo");)
= Count ( [tableData]![field1] = "foo" )

You can't use a sql string as the controlsource for a text box. Use a domain
aggregate function instead, e..g

=DCount("*", "tableData", "field1='foo'")

[Bob Quintal]

th*******@hotmail.com (Cro) wrote in
news:24*************************@posting.google.co m:

Hello Access Developers,

I'd like to know if it is possible to perform a count in an
expression that defines a control source.

My report is based on a query. In my report, I want a text box
to display the number of times a certain value appears in a
certain field (i.e. perform a ‘count'). I will be doing this
for many values in many fields so do not wish to have scores
of queries to build my report.

I have tried setting the control source property of the text
box to several expressions (none of which have worked):

SELECT Count(*) AS Counted FROM tableData WHERE
(tableData.field1="foo");
=(SELECT Count(*) AS Counted FROM tableData WHERE
(tableData.field1="foo");)
= Count ( [tableData]![field1] = "foo" )

MS Access' help file tells me that I can set the control
source property to an expression, which is a combination of
operators, field names, functions, literals, and constants
that evaluates to a single value. It also says that
expressions can specify criteria (such as Order Amount>10000)
or perform calculations on field values (such as
Price*Quantity).).

Question 1) Is there an expression that will count the number
of times a certain value appears in a certain field in the
underlying query the report is based on?

Question 2) If so, how would it then be possible to add these
values up in another text box? For example, text box ‘A' and
text box ‘B' count the number of times the value "a" and "b"
appear in ‘field1' of the underlying table ‘tableData'
respectively. Could then a third text box ‘C' sum the values
calculated for text box A and text box B?

Thank you greatly for your wisdom and expertise.

Regards,

Cro

Your expression is =DCount("tableData","[field1] = 'foo'")

two ways:
=DCount("tableData","[field1] = 'foo'") + DCount
("tableData","[field1] = 'bar'")

or

=field1.value+field2.value

The first works, the second sometimes works.
Bob Quintal

[fredg]

See my comments in line below....

On 24 Jun 2004 03:38:45 -0700, Cro wrote:

Hello Access Developers,

I'd like to know if it is possible to perform a count in an expression
that defines a control source.

My report is based on a query. In my report, I want a text box to
display the number of times a certain value appears in a certain field
(i.e. perform a ‘count'). I will be doing this for many values in many
fields so do not wish to have scores of queries to build my report.

I have tried setting the control source property of the text box to
several expressions (none of which have worked):

SELECT Count(*) AS Counted FROM tableData WHERE
(tableData.field1="foo");
=(SELECT Count(*) AS Counted FROM tableData WHERE
(tableData.field1="foo");)
= Count ( [tableData]![field1] = "foo" )
You CANNOT use SQL in a control source expression.
MS Access' help file tells me that I can set the control source
property to an expression, which is a combination of operators, field
names, functions, literals, and constants that evaluates to a single
value. It also says that expressions can specify criteria (such as
Order Amount>10000) or perform calculations on field values (such as
Price*Quantity).).
You can certainly use an expression... just not SQL.

Question 1) Is there an expression that will count the number of times
a certain value appears in a certain field in the underlying query the
report is based on?
If you wish to 'Count' the number of times a field ='foo' appears in
the report, then you could use:

=Sum(IIf([Fieldname] = "foo",1,0)

which adds 1 for each time it appears.

If you wish to count how many times a field = 'foo' appears in the
underlying table/query (which may be more than the number of times it
appears in the report due to additional filtering):

=DCount("*","[QueryName]","[FieldName] = 'foo'")

Note the use of double and single quotes in the where clause.
Question 2) If so, how would it then be possible to add these values
up in another text box? For example, text box ‘A' and text box ‘B'
count the number of times the value "a" and "b" appear in ‘field1' of
the underlying table ‘tableData' respectively. Could then a third text
box ‘C' sum the values calculated for text box A and text box B?
Repeat the individual calculations and add them up in any report
section except the Page Header/Footer sections.
Either:
=Sum(IIf([FieldName] = "foo" Or [FieldName] = "apples",1,0))

Note: The above will return just the count within the group if used in
a Group Header/Footer. It will return the count for all records if
used in the Report Header.Footer.

Or...

=DCount("*","[QueryName]","[FieldName] = 'foo'") +
DCount("*","[QueryName]","[FieldName] = 'apples'")

If you wanted to display the sum in the Page Header/Footer you would
then sum each individual criteria in the detail section (as in
Question 1 above), then in the Page Header/Footer you would use the
name of the control that did the calculation:

=[ControlA] + [ControlB] + [ControlC] + etc.
Thank you greatly for your wisdom and expertise.

Regards,

Cro

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[Cro]

Thank you John, Bob and fredg for you excellent answers. I will try
all these methods over the weekend and post back to let you know how I
got on.

[Cro]

th*******@hotmail.com (Cro) wrote in message news:<24**************************@posting.google. com>...

Thank you John, Bob and fredg for you excellent answers. I will try
all these methods over the weekend and post back to let you know how I
got on.

I was able to acheive exactly what I wanted using domain aggregate
functions so thank you all very much for your excellent answers.

Now that I'm aware of domain aggregate functions, I'll be using much
more of them.