By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,845 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

DSum, Function Methods - Need Assistance

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.TotalABC

HTH
- Turtle

"Dalan" <ot***@safe-mail.net> wrote in message
news:50**************************@posting.google.c om...
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 discussion thread is closed

Replies have been disabled for this discussion.