473,841 Members | 1,792 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.fiel d1="foo");
=(SELECT Count(*) AS Counted FROM tableData WHERE
(tableData.fiel d1="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
Nov 13 '05 #1
5 18292
"Cro" <th*******@hotm ail.com> wrote in message
news:24******** *************** **@posting.goog le.com...
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.fiel d1="foo");
=(SELECT Count(*) AS Counted FROM tableData WHERE
(tableData.fiel d1="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='fo o'")
Nov 13 '05 #2
th*******@hotma il.com (Cro) wrote in
news:24******** *************** **@posting.goog le.com:
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.fiel d1="foo");
=(SELECT Count(*) AS Counted FROM tableData WHERE
(tableData.fiel d1="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("tableD ata","[field1] = 'foo'")

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

or

=field1.value+f ield2.value

The first works, the second sometimes works.
Bob Quintal
Nov 13 '05 #3

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.fiel d1="foo");
=(SELECT Count(*) AS Counted FROM tableData WHERE
(tableData.fiel d1="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.
Nov 13 '05 #4
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.
Nov 13 '05 #5
Cro
th*******@hotma il.com (Cro) wrote in message news:<24******* *************** ****@posting.go ogle.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.
Nov 13 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
2284
by: Mark Lees | last post by:
I've created some fields that calculate future dates. The way they are set up they do not save to a table. This is the expression I used (=DateSerial(Year(),Month()+6,Day(). I placed it in the Control Source property. I want to save them to a table so I can use them on a report. So the 2 questions are: 1. How do you use a calculated field that doesn't save to a table in a report?
2
4952
by: Gwin | last post by:
I am programming a client service dbase in access 2000 for a non-profit health clinic. I want to create a navigation menu that can show varying numbers of command buttons with varying captions, driven by data from a table. 1. First, the user selects a "client" by code. This opens a navigation form which offers command buttons for each of the services the client is enrolled in, e.g., nutrition, pre-natal care, drug treatment, etc. ...
9
7436
by: Jack | last post by:
In the control source of a textbox, is there a way to refer to a column of a combobox? For example: =.Column(2) Thanks, Jack
2
4921
by: jerry.ranch | last post by:
I've been using row source with the QBE for my list and combo boxes..when would I use control source? jerry
2
2735
by: HeroinNO.4 | last post by:
Hello everyone! Now the latest version of free count down timer source code is available in http://www.fillweb.com/countdown.htm, you can open it in IE and View->Source to see the latest version source code, also you may copy the code below and save in a ".htm" file, and run it in browser, a cool count down timer will show you ! <html> <head> <meta http-equiv="Content-Language" content="en-us"> <meta http-equiv="Content-Type"...
7
3311
by: HeroinNO.4 | last post by:
Hello guys, free count down timer source code has updated to 06/11/27, you can copy the code below and save in a ".htm" file and run it in a browser support javascript 1.1 or later, or you can open http:\\www.fillweb.com\countdown.htm, if you are using IE, you may View->Source to see the latest version of source code ! <html> <head> <meta http-equiv="Content-Language" content="en-us">
4
18833
by: Anja | last post by:
Hi everyone, I am trying to use the expression builder to create input to a control in an Access report. I have a table called Records and I want to select the minimum date for a record where the student = studentID.. The sql is pretty simple: "Select MIN(RecDate) from records_T where studentID = 1".
2
1505
by: vsteshenko | last post by:
Hello, This is my first post so I hope I'm doing this correctly. I am currently working on creating an order form for sales associates at my work to be used at conventions. I have a form with a subform. On the main form, there is a text box that displays the sum of total orders entered in the subform, . I'm trying to create another text box on the main form, that looks at the subtotal text box and depending on the value, displays...
4
13948
by: Lou O | last post by:
Is it possible to use the row (index) of a list box as control source Property for a text box? Example: Text1.ControlSource Property is set to "= List1.Column(0,2)" in design view. When I open the form, Text1 does not display the value of List1.Column(0,2) Am I missing something?
0
9706
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10932
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10609
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10303
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9446
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7025
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5692
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5881
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3140
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.