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 5 18281
"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'") 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
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.
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. 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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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?
|
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. ...
|
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
|
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
|
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"...
| |
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">
|
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".
|
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...
|
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?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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,...
| |
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...
|
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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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();...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |