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

DSum Assistance for Building Expression

P: n/a
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 with NZ to provide the
subtotals (QSub) for the individual rows.

What I'm trying to accomplish is to use a DSum expression to tally the
(QSub)'s by like (IDRef) numbers. Currently, if there is only one row
with the same IDRef then the (QSub) represents all cost for it and
does post correctly to (sfrmCost) which in turn is posted to the
(TotalItemCost) text box on the main form (frmMain). The
(TotalItemCost) box on (frmMain) uses
=[Forms]![frmMain]![sfrmCost]![RefCost] to obtain the total from
(sfrmCost).

I have also tried using DSum expressions in the control source of the
(RefCost) of (sfrmCost) and in the control source of the
(TotalItemCost) of (frmMain), but this hasn't work either.

Excuse me, but it seems that I have created a somewhat convoluted
tangent above, so allow me to recap:

Table: (tblItems)
(IDRef) - a manually keyed ID # referencing a record on the
main form

Query: (qryItems)
(QSub) - contains the subtotal of one calculated row of three
items - Dsum needed to calculate the QSub totals by the same (IDRef)
when there is more than one row of same.

SubForm: (sfrmCost) - shows all IDRef's and other columns from
tblItems -
(RefCost) - a text box that currently shows the total of
=[Col1]+[Col2]+[Col3] by individual row

Form: (frmMain) - to show the final result of the DSum total by ID
reference.
(TotalItemCost) - the text box to show the total cost for all,
but currently only a one row occurrence using:
=[Forms]![frmMain]![sfrmCost]![RefCost]

Perhaps I should ask which is better to use: an intermediate query or
a control source? Here are a couple of DSum expressions that I have
used:

' in Query =DSum("[QSub]","qryItems","[IDRef] = & Me![IDRef]")

' on Main Form TotalItemCost=DSum("[QSub]","qryItems","[IDRef]=
[Forms]![frmMain]![sfrmCost]![RefCost]")

Any assistance provided will be greatly appreciated. Thanks, Rolan
Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.