subtotal only specific value in a report

[lake2212]

How would I subtotal a field called 'Number of Shares' only if another field
called 'Type' is equal to the value "Stock"?

Thanks.
Julie

 

[Arvin Meyer [MVP]]

Try using an expression as the controlsource of a textbox in the form or
subform's footer:

=IIf([Type]="Stock", Sum([Number Of Shares], "")

 

[lake2212]

I am getting an error "the expression you entered has a function containing
the wrong number of arguments". I'm new to this and am not sure where the
parenthesis is missing.
Thanks.
Julie

Try using an expression as the controlsource of a textbox in the form or
subform's footer:

=IIf([Type]="Stock", Sum([Number Of Shares], "")
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

How would I subtotal a field called 'Number of Shares' only if another
field
called 'Type' is equal to the value "Stock"?

Thanks.
Julie

 

[John W. Vinson]

How would I subtotal a field called 'Number of Shares' only if another field
called 'Type' is equal to the value "Stock"?

Thanks.
Julie

Base the report on a Query. In the query, include a calculated field:

StockShares: IIF([Type] = "Stock", [Number of Shares], 0)

In the report footer (or section footer) you can then total StockShares.

 

[John Spencer]

It's possible that what you want is

=IIf([Type]="Stock", Sum([Number Of Shares]), "")


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I am getting an error "the expression you entered has a function containing
the wrong number of arguments". I'm new to this and am not sure where the
parenthesis is missing.
Thanks.
Julie

Try using an expression as the controlsource of a textbox in the form or
subform's footer:

=IIf([Type]="Stock", Sum([Number Of Shares], "")
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

How would I subtotal a field called 'Number of Shares' only if another
field
called 'Type' is equal to the value "Stock"?

Thanks.
Julie

 

[Arvin Meyer [MVP]]

After, [Number Of Shares], it should be:

=IIf([Type]="Stock", Sum([Number Of Shares]), "")

I missed 1
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I am getting an error "the expression you entered has a function containing
the wrong number of arguments". I'm new to this and am not sure where the
parenthesis is missing.
Thanks.
Julie

Try using an expression as the controlsource of a textbox in the form or
subform's footer:

=IIf([Type]="Stock", Sum([Number Of Shares], "")
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

How would I subtotal a field called 'Number of Shares' only if another
field
called 'Type' is equal to the value "Stock"?

Thanks.
Julie

 

[Ken Sheridan]

Julie:

Is this in a report? If so then one way would be to hide the control by
putting the following code in the Format event procedure of the section
(group footer?) in which the control is located:

Dim blnShowTotal As Boolean

blnShowTotal = ([Type] = "Stock")

Me.[txtTotalShares].Visible = blnShowTotal

where txtTotalShares is the name of the control in question. If the control
has an associated label you can hide that too if you wish, e.g.

Me.[lblTotalShares].Visible = blnShowTotal

You can then simply leave the control's ControlSource as:

=Sum([Number of Shares])

However, your question is a little ambiguous, so just to be clear that this
would be what you want, it would show you a subtotal of the Number of Shares
in the group footer where the value of Type is 'Stock' if the report is
grouped by Type, and not in the group footer's for other values of Type.

Another possible interpretation of your question is that you want to Sum the
Number of Shares column but only include the values in the summation for
those rows where Type = 'Stock'. In this case you'd not hide the control,
but would make its ControlSource:

=Sum([Number of Shares] * IIf([Type] = "Stock",1,0))

This would do the same as summing the computed column suggested by John
Vinson. The way it works is that the IIf function would return 1 if Type =
'Stock', but 0 otherwise, so when you multiply the value of Sum of Shares by
the 1 or 0 you get the value if Type is Stock but zero if not. Summing these
values gives the sum of just the values where Type = 'Stock' because the
zeros add nothing to the final figure of course.

Ken Sheridan
Stafford, England

 

[lake2212]

Great, thanks. This worked for me.

After, [Number Of Shares], it should be:

=IIf([Type]="Stock", Sum([Number Of Shares]), "")

I missed 1
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I am getting an error "the expression you entered has a function containing
the wrong number of arguments". I'm new to this and am not sure where the
parenthesis is missing.
Thanks.
Julie

Try using an expression as the controlsource of a textbox in the form or
subform's footer:

=IIf([Type]="Stock", Sum([Number Of Shares], "")
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

How would I subtotal a field called 'Number of Shares' only if another
field
called 'Type' is equal to the value "Stock"?

Thanks.
Julie