473,888 Members | 1,437 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

DSum, Function Methods - Need Assistance

I have tried both methods of using DSum and creating a Function to
address summing some number columns, but to no avail. Since this has
been a popular topic over the years, I'm sure I'll receive some
guidance. This is related to Access 97 and involves my redoing a
miscellaneous purchase and sales form.

A main form (frmMain) is used that contains summary information of the
item. I incorporated two continuous subforms (sfrmPurchases) and
(sfrmSales). Of course, I also created two new tables for the subforms
(tblPurchases) and (tblSales). And also created three queries
(qryPurSales) and (qryPurchases), (qrySales) - the latter two use the
Sum function to group by ID and sum by the columns. The relationships
are the main form table as one to the two subform tables as many.

Everything seems to work okay, except that I'm having difficulty
sorting out the DSum expressions to use to provide some of the column
totals. Additionally, I have summary section at the top on the main
form showing the grand total results of the subforms along with more
calculations.

The three column totals that I having difficulty with all probably
require a similar expression. It is the sfrmSales TotalSales, Discount
and NetSales columns that do not total. The totals across are okay,
but the grand total at the bottom of each is not. As a side note, one
of the expressions I have used for the Purchases to be summarized in
the main form section which works is:
=DSum("[Purchases]","tblPurchases ", "[IDPur] = " & [IDMain])

I also tried using a Function module to open a SQL recordset, but
could not get it to work either. I have substituted many variations of
(IDSales), (tblSales), (qryPurSales) and (qrySales) in my expressions.

Any assistance will be greatly appreciated. Thanks.
Nov 12 '05 #1
1 4807
Say you have a subform named sfm1; its recordset includes a field named ABC
which you want to total and show on the main form.
Put a textbox on the footer of the subform (the textbox, or even the entire
footer can be set to visible=false).
Let's call it TotalABC; set its ControlSource like this:
=Sum(ABC)
Now put a textbox named MainABC on your main form; set its ControlSource
like this:
=sfm1.Form.Tota lABC

HTH
- Turtle

"Dalan" <ot***@safe-mail.net> wrote in message
news:50******** *************** ***@posting.goo gle.com...
I have tried both methods of using DSum and creating a Function to
address summing some number columns, but to no avail. Since this has
been a popular topic over the years, I'm sure I'll receive some
guidance. This is related to Access 97 and involves my redoing a
miscellaneous purchase and sales form.

A main form (frmMain) is used that contains summary information of the
item. I incorporated two continuous subforms (sfrmPurchases) and
(sfrmSales). Of course, I also created two new tables for the subforms
(tblPurchases) and (tblSales). And also created three queries
(qryPurSales) and (qryPurchases), (qrySales) - the latter two use the
Sum function to group by ID and sum by the columns. The relationships
are the main form table as one to the two subform tables as many.

Everything seems to work okay, except that I'm having difficulty
sorting out the DSum expressions to use to provide some of the column
totals. Additionally, I have summary section at the top on the main
form showing the grand total results of the subforms along with more
calculations.

The three column totals that I having difficulty with all probably
require a similar expression. It is the sfrmSales TotalSales, Discount
and NetSales columns that do not total. The totals across are okay,
but the grand total at the bottom of each is not. As a side note, one
of the expressions I have used for the Purchases to be summarized in
the main form section which works is:
=DSum("[Purchases]","tblPurchases ", "[IDPur] = " & [IDMain])

I also tried using a Function module to open a SQL recordset, but
could not get it to work either. I have substituted many variations of
(IDSales), (tblSales), (qryPurSales) and (qrySales) in my expressions.

Any assistance will be greatly appreciated. Thanks.

Nov 12 '05 #2

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

Similar topics

1
2527
by: Rolan | last post by:
I'm using Access 97 and need some assistance in sorting out a proper DSum expression. It relates to a subform (sfrmCost) from which I'm wanting to extract the grand total of any and all data rows that have the same primary IDReference, and have it post as an accumulative sum in one text box (TotalCost) on the main form (frmMain). There are actually three columns across in each row which contain number values and I have been able to only...
0
3358
by: Rolan | last post by:
I'm using Access 97 and need some assistance in sorting out a proper DSum expression, or maybe even DCount might be an alternative. I have tried numerous combinations, but with no apparent success. The DSum function relates to a subform (sfrmCost) with a text box (RefCost) which is intended to be the container for a total from a query (qryItems). The query has three number columns from the table (tblItems) of which I have an expression...
2
11603
by: Dalan | last post by:
I seemed to be having problems with structuring the use of NZ with a DSum expression. Having tried numerous variations of the expression without success, I'm asking for assistance. First some background (Access 97) - the DSum expressions are being used in grand total text boxes on the footer of a subform. And when viewing the subform in the linked main form the two grand total boxes display #Error if no entry had been made. Those with...
4
2864
by: Dalan | last post by:
Perhaps someone can share information on the methods to use to effect the automation process of creating the property to set the AllowBypassKey function. I was directed to: http://www.mvps.org/access/general/gen0040.htm and downloaded the function module that includes setting the fourth DDL part to true. It initially failed to compile using Access 97 because "PropType As DAO.DataTypeEnum" is not an automation type that Visual Basic...
3
3779
by: Mark Reed | last post by:
All, I have never used this function before and am not sure it what I need. Just to clarify, I have a report based on a query which has amoungst other field, wk, parea & packs_req. What I am trying to do is create a subreport for the main report which gives a generalised breakdown of the main report. For the gender of 'Ladies', there may be several parea's so I want to sum the packs_req for ladies only. (Gender is not a field I can use,...
1
1945
by: Marc Aube | last post by:
Is there a web site that can ofer some help. The quotation marks are posing an issue as well as other items for this function. I have some books but they are not consistent in their use of the code lingo. Thank you for any help. Presently I have tables and forms to gather employee info. I use the subform to gather status of absence, text field. What I want to do is calculate the vacation hours and subtract them from the total alocation of...
1
4450
by: phaddock4 | last post by:
Being fairly inexperienced at Access 2000, i've been reading many posts here for the last several days, and testing myself to find the best approach to do the following in A2K: SET UP: I have created a MainTable (and related form), which has an associated SubForm (popup) along with its underlying, separate Table. The tables' relationship is one to many respectively. The primary key
3
4708
patjones
by: patjones | last post by:
Good morning all: In what seems like an ongoing saga to make the DSum function do what I need it to, I am now having trouble with a user-defined function in my VBA module. Here's the offending line of code: Me!txtLostDaysNoPaySeasonal = DSum("getNumberOfWeekdays(!, !)", "qryTest", "! = 'BK21312'") In this, getNumberOfWeekdays is a function that I define elsewhere in the module, as such: Function getNumberOfWeekdays(dteStartDate...
2
3402
jmoudy77
by: jmoudy77 | last post by:
Hi, I'm trying to use a variable "FirstSemi" as the second criteria in a DSum function. The DSum function is used in the same Form_Load function that the variable was declared. I keep getting an error referencing the variable in the DSum function when the form loads. Any ideas? And > FirstSemi") -Josh
0
9800
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
11181
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...
1
10886
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 most users, this new feature is actually very convenient. If you want to control the update process,...
0
9597
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
7148
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
5819
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
6014
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4245
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3252
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.