In a report - can this be done

  • Thread starter Thread starter Cyndi
  • Start date Start date
C

Cyndi

I have a report of contracts - one group starts with an F#sequence and
the other group starts with a X#sequence

In the report I have them sorted and grouped to where the f's are
together and in numerical order and the x's are the same - i then have
a footer which subtotals the groups

what I want to do now is take this subtotal and use in a calculation
at the bottom of the report - I know I saw where these group subtotals
can't be used but can you maybe write a function that says the total
where the first letter is F??

Please don't laugh if this is dumb question - I am an accountant
teaching myself access.


Thanks in advance for all help.
 
Hi Cyndi,

I don't think that you can use the subtotals directly,
but you can recreate them in the form's footer using the
DSum() function. The DSum() function will sum all field
values meeting a specified condition.

The DSum format is as follows:

DSum
("[FieldNameToBeSummed]","[TableNameContainingSumField]",C
riteria)

The format of the criteria statement is similar to the
WHERE statement of a query, but without the word WHERE.

For example, if you have a table tblContracts, with the
field ContractNo (which starts with an F or an X), and
the field ContractAmt (which you want to sum), the
following would give you the total of all contracts
starting with F (if I typed this correctly):

DSum("[ContractAmt]","tblContracts","left([ContractNo],1)
= 'F'")

Replacing the F with an X would give you the total for
the X contracts.

Note also that if you want to show a reference to this
total withing the detail section of the report (if you
had such a need) you could have the criteria
automatically change based on the grouping of the current
detail contract number by concatenating the criteria
string as follows:

DSum("[ContractAmt]","tblContracts","left([ContractNo],1)
= '" & left(rptControlName,1) & "'")

where rptControlName would be replaced with the name of
the control in the report detail section containing the
Contract Number.

HTH

-Ted Allen
 
Cyndi said:
I have a report of contracts - one group starts with an F#sequence and
the other group starts with a X#sequence

In the report I have them sorted and grouped to where the f's are
together and in numerical order and the x's are the same - i then have
a footer which subtotals the groups

what I want to do now is take this subtotal and use in a calculation
at the bottom of the report - I know I saw where these group subtotals
can't be used but can you maybe write a function that says the total
where the first letter is F??

Please don't laugh if this is dumb question - I am an accountant
teaching myself access.


Thanks in advance for all help.
Click to expand...

In the report footer, you can use Sum() expressions that subtotal only
the sets of records you want. For example:

=Sum(IIf(Left([ContractNo], 1)='F', 1, 0) -
Sum(IIf(Left([ContractNo], 1)='X', 1, 0)

Note that the above expression will probably have been wrapped by the
newsreader, but was entered all on one line. It gives the difference
between the number of "F" contracts and the number of "X" contracts.
 
Back
Top