I have a query written in MS Access that has a few calculated fields. Is it
possible to refer to that query in a form field. I'd like the form field to
show the sum of one of the columns from the query I've written. I've tried
to put the following in the form field's control source property but I'm
getting an error. Query1 is the name of the query and CalculatedTotal is
the calc'd field column I'm trying to get a total for in the form field.
=Sum([Query1]!CalculatedTota l)
Many Thanks.
3  29004 
Thanks Rick. I had never seen DSUM used in that context before. It worked
like a charm. You're awesome!
Thanks again!
"Rick Brandt" <RB*****@Hunter .Com> wrote in message
news:bj******** ****@ID-98015.news.uni-berlin.de... "MX1" <mx*@mx1.abc> wrote in message
news:YV******** *************@r wcrnsc51.ops.as p.att.net... I have a query written in MS Access that has a few calculated fields.
Is it possible to refer to that query in a form field. I'd like the form
field to show the sum of one of the columns from the query I've written. I've
tried to put the following in the form field's control source property but I'm
getting an error. Query1 is the name of the query and CalculatedTotal
is the calc'd field column I'm trying to get a total for in the form field.
=Sum([Query1]!CalculatedTota l)
The standard aggregate functions only work on the current bound RecordSet
and the aggregation is based on where they are used (Group Header/Footer, Form
Header/Footer, etc.). In your case Sum([CalculatedTotal ) would work if the form was
actually bound to the query [Query1] and you placed the control in either the Form Header
or Form Footer.
If your form is bound to a different data source and you just want to
display this sum on your form then you need to use a Domain Aggregate function as these
provide an argument for the DataSource to look at. In your case...
=DSum("[CalculatedTotal]", "[Query1]")
...should work. Domain Aggregate function have no concern about their
placement either. You could place the control anywhere you like on your form and it
will return the same result.
One more question, I'd like to make the new calcualted form field only add
records associated with the client currently selected on the form. As I
scroll through the clients, I'd like the DSUM to change accordingly. I'm
wondering if I should filter at the original query or the fields on the
form? Any thoughts?
"MX1" <mx*@mx1.abc> wrote in message
news:7m******** ***********@rwc rnsc52.ops.asp. att.net... Thanks Rick. I had never seen DSUM used in that context before. It
worked like a charm. You're awesome!
Thanks again!
"Rick Brandt" <RB*****@Hunter .Com> wrote in message
news:bj******** ****@ID-98015.news.uni-berlin.de... "MX1" <mx*@mx1.abc> wrote in message
news:YV******** *************@r wcrnsc51.ops.as p.att.net... I have a query written in MS Access that has a few calculated fields. Is it possible to refer to that query in a form field. I'd like the form field to show the sum of one of the columns from the query I've written. I've tried to put the following in the form field's control source property but
I'm getting an error. Query1 is the name of the query and CalculatedTotal is the calc'd field column I'm trying to get a total for in the form
field.
=Sum([Query1]!CalculatedTota l)
The standard aggregate functions only work on the current bound
RecordSet and the aggregation is based on where they are used (Group Header/Footer, Form Header/Footer, etc.). In your case Sum([CalculatedTotal ) would work if the form was
actually bound to the query [Query1] and you placed the control in either the Form
Header or Form Footer.
If your form is bound to a different data source and you just want to display this sum on your form then you need to use a Domain Aggregate function as
these provide an argument for the DataSource to look at. In your case...
=DSum("[CalculatedTotal]", "[Query1]")
...should work. Domain Aggregate function have no concern about their placement either. You could place the control anywhere you like on your form and
it will return the same result.
All set, I filtered by ClientID on the dsum in the form and it worked. The
problem was that I didn't have ClientID selected as a field in the original
query. Thanks again!
"MX1" <mx*@mx1.abc> wrote in message
news:lB******** *************@r wcrnsc51.ops.as p.att.net... One more question, I'd like to make the new calcualted form field only add
records associated with the client currently selected on the form. As I
scroll through the clients, I'd like the DSUM to change accordingly. I'm
wondering if I should filter at the original query or the fields on the
form? Any thoughts?
"MX1" <mx*@mx1.abc> wrote in message
news:7m******** ***********@rwc rnsc52.ops.asp. att.net... Thanks Rick. I had never seen DSUM used in that context before. It worked like a charm. You're awesome!
Thanks again!
"Rick Brandt" <RB*****@Hunter .Com> wrote in message
news:bj******** ****@ID-98015.news.uni-berlin.de... "MX1" <mx*@mx1.abc> wrote in message
news:YV******** *************@r wcrnsc51.ops.as p.att.net...
> I have a query written in MS Access that has a few calculated
fields. Is it > possible to refer to that query in a form field. I'd like the form
field to > show the sum of one of the columns from the query I've written.
I've tried > to put the following in the form field's control source property but I'm > getting an error. Query1 is the name of the query and
CalculatedTotal is > the calc'd field column I'm trying to get a total for in the form
field. >
> =Sum([Query1]!CalculatedTota l)
The standard aggregate functions only work on the current bound
RecordSet and the aggregation is based on where they are used (Group Header/Footer, Form
Header/Footer, etc.). In your case Sum([CalculatedTotal ) would work if the form was
actually bound to the query [Query1] and you placed the control in either the Form
Header or Form Footer.
If your form is bound to a different data source and you just want to
display this sum on your form then you need to use a Domain Aggregate function as
these provide an argument for the DataSource to look at. In your case...
=DSum("[CalculatedTotal]", "[Query1]")
...should work. Domain Aggregate function have no concern about their
placement either. You could place the control anywhere you like on your form
and it will return the same result.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: mike |
last post by:
Ok, I have a page that queries a database and retrieves values to
create a custom report in ms excel format on the fly so that it can be
downloaded. I can use the following sub to generate the report just
fine. It works correctly for every field in the database and creates
the report as desired except for one field and one field only. The...
|
by: Steve Jorgensen |
last post by:
Hi all,
I've just finished almost all of what has turned out to be a real bear of a
project. It has to import data from a monthly spreadsheet export from another
program, and convert that into normalized data. The task is made more
difficult by the fact that the structure itself can vary from month to month
(in well defined ways).
So,...
|
by: Steve |
last post by:
I have looked through the newsgroup for an answer to this but haven't
been able to find anything resembling my situation.
What I want to do is relatively simple, I think. I have a crosstab
query that is bound to a report I am creating. I would like the
column headings to be the name of the correspoding field name. For
example, I have...
|
by: ssb |
last post by:
Hello,
This may be very elementary, but, need help because I am new to access
programming.
(1) Say, I have a column EMPLOYEE_NAME. How do I fetch (maybe, cursor
?) the values one by one and populate a combo box with these names.
(this way, I can display all the EMPLOYEE_NAME values)
(2) In general, can I do additional processing on...
|
by: nyt |
last post by:
I have a problem of number and text field.
I got the database file(mdb) that contains many combo boxes used and
its list values are created by "value list"
For eg field
Field name= 'furniture' , data type='Number' ,Display Control='Combo
Box', RowSource Type = 'Value List' and
Row Source = ' 0;"chair";1;"Table";2;"Bed" '
Therefore, in...
| | |
by: Steve |
last post by:
I realize that this probably isn't a best practice, but I'm working with
legacy code that has a query stored in one column of a table. Because
the queries vary, the JSP page that selects a query and runs it cannot
add any additional information (like a WHERE clause).
I need to add a few more records to the the table, and would like the...
|
by: Jimmy Stewart |
last post by:
I have a continuous form has an unbound field named "Client". Its
controlsource is " =.(1) " This allows me to display the
client name without the combo box dropdown arrow. I want users to be able to
sort the records by the client name but I am unable to figure out how to
reference this in my code.
If I used the following code, I can sort...
|
by: Exick |
last post by:
This is more of a minor annoyance/curiosity than a real problem, but I'm wondering if anyone here can provide some answers.
I have a form bound to a table with lots of controls on it that are bound to fields of said table. I also have a combo box on the form that is unbound. I'm using it purely as a search mechanism. Anyway, the combo box...
|
by: Clare CAVS |
last post by:
I have a table with a lookup column referring to another table .
tblRooms has two fields, (Autonumber), and . The column I want to display is the RoomName column. If I have Bound Column = 1, and Column Count = 2, Column Widths 0cm;3cm I get the display I want.
However, if I change the Bound Column to 2, this changes the values displayed...
|
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...
|
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. ...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
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...
|
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...
|
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...
|
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...
|
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
| | |
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...
| |
