A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Conditional Totals in Subform



 
 
Thread Tools Display Modes
  #1  
Old February 11th, 2010, 03:39 PM posted to microsoft.public.access.forms
Paulo
external usenet poster
 
Posts: 51
Default Conditional Totals in Subform

I have a main form with several combo boxes, and a subform. Based on the
selection made in the combo boxes, the corresponding records are shown in the
subform. There are also totals (and calculations as well, like
Revenue-Costs=Margins) in the subform footer, using Text Boxes. These totals
are based only on the records shown, so they change with each new combination
of selections in the combo boxes in the main form. Everything is fine so far.
But what if I want to show a conditional total in the subform footer, that
is, sum only those records (from the ones currently shown) that meet a
specific criteria?

I tried to do this using the Dsum function in a text field, but I am
struggling to find the right arguments for the function. The first argument
is not difficult, it is just the numeric field(s) that you want in the total.
But how can I define correctly the second (domain) and third (criteria)
argument, if I am referring to only the current set of records (filtered by
the combo boxes) shown in the subform (and not the whole query on which the
subform is based).

For example, I might add the following code to each of the combo boxes:

Private Sub Combo_Channel_Lostfocus()
With Me.[Sub Bids Tracking].Form ‘the name of the subform
.TextBox123 = DSum("[Quantity]*[Unit Price]", Second Argument, Third
Argument)
End With

End Sub
Can you make any suggestions? Is my explanation of the problem clear enough?
Thanks in advance,

Paulo

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:49 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.