Totals, Sums, DSums

  • Thread starter Thread starter Christine Abbey
  • Start date Start date
C

Christine Abbey

Okay,

I can't seem to get any of these to work. On a subform I
have created, I have a Totals field, which is a calc
adding the Book Field and the Tuition field, both of which
are calcs of multiplying the book amt or Tuition Amt field
and the percentage. Clear as mud so far?

I need to create an Accrual total only for the current
year. I have created two invisible fields called Year to
AP which is calced from a date field on the form, and Year
Now, which is the System Date year.

I cannot get either a sum or a Dsum to work in the footer,
in the detail or in the main form.

Any Ideas????
 
Hi Christine

It's usually better to do as much per-line calculation as popssible in the
RecordSource query, then the footer aggregates (sums etc) can be done on
fields rather than expressions.

So, I suggest you add a field to your query:
ThisYearAmt: IIf(Year([DateField])=Year(Date()), [AmountField],0)

IOW, use the amount field if the date is this year, otherwise zero.

You can then put the following in your textbox:
=Sum(ThisYearAmt)
 
Learn something new everyday. Thanks so much!
-----Original Message-----
Hi Christine

It's usually better to do as much per-line calculation as popssible in the
RecordSource query, then the footer aggregates (sums etc) can be done on
fields rather than expressions.

So, I suggest you add a field to your query:
ThisYearAmt: IIf(Year([DateField])=Year(Date()), [AmountField],0)

IOW, use the amount field if the date is this year, otherwise zero.

You can then put the following in your textbox:
=Sum(ThisYearAmt)

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Okay,

I can't seem to get any of these to work. On a subform I
have created, I have a Totals field, which is a calc
adding the Book Field and the Tuition field, both of which
are calcs of multiplying the book amt or Tuition Amt field
and the percentage. Clear as mud so far?

I need to create an Accrual total only for the current
year. I have created two invisible fields called Year to
AP which is calced from a date field on the form, and Year
Now, which is the System Date year.

I cannot get either a sum or a Dsum to work in the footer,
in the detail or in the main form.

Any Ideas????
Click to expand...


.
Click to expand...
 
Back
Top