• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

Access 2003: Report - Group Totals

Hi EE,

Would like to group total, the number of credits appearing on each detail line....

However, in the detail processing, am setting the credits field on the report to an alpha literal
if it's value is 0.

So am concerned what the group total will total, the underlying field, or the literal.

So should i code for the group total credits something like this:

=Sum(IIf(IsNumeric([[Credits Earned]),[Credits Earned],0)

Pls advise, tx, sandra
0
mytfein
Asked:
mytfein
  • 2
  • 2
3 Solutions
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:

In Access 2003 you should be able to  use:

=Sum(IIf(IsNumeric([[Credits Earned]),[Credits Earned],0)

To convert a literal string to a numeric value use the Val() function.

Depending on your data you might be able to use this:

=Sum(Val([Credits Earned]))



0
 
Simon BallCommented:
i think you will be performing the sum on the underlying data set for the report, and not the control on which you have performed the switch to alpha?

so your sum should work.
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Try:

=Sum(IIf(IsNumeric([[Credits Earned]),Val([Credits Earned]),0)
0
 
mytfeinAuthor Commented:
Tx guys for writing,

truthfully, i got this idea from another EE post....

i don't understand how the Access grouping mechanism knows to do this SUM statement
with an implicit GROUP BY

Also, normally i like writing SQL bec. if feel more control but what the diff bet:
=SUM   and   =DSUM  (btw, i notice that DSUM does have a where parameter)

pls advise, tx, s
0
 
mytfeinAuthor Commented:
Hi Guys,

thank you HiTech for teaching about the val function. Did not know about it, and it's great bec. it
works with data type double which i needed.

so i'm trying HiTech's statement of:

=Sum(IIf(IsNumeric([[Credits Earned]),Val([Credits Earned]),0)

in a control in the group footer, and when i click ok, the control source of the control does not update
to the above formula....

if you have a minute, below is the mdb, can u try iupdateing the footer control pls?

tx, s
2011-03-25-EE-sum-.zip
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now