Text Box Expression for Selective Total from Subform

[daveam]

My Access 2003 database tracks immigration records and related
expenses. I have a main form displaying employee data, and a tab
control thereon where each tab contains a subform. The Expenses
subform, for example, lists costs of certain documents like Visas and
Greencards. I'd like to display two totals on my main form: Total
amount from the Expenses subform for Visa-related expenses, and total
amount for Greencard expenses. I currently have a textbox in the
footer of the Expense subform that totals ALL expenses, and I can
reference that from a textbox on the main form... no problem. Also in
the footer, I tried to total ONLY the Greencard expenses for the
employee (i.e. the record displayed on the main form) by using this
expression in a text box:

=DSum("[curExpenseAmount]","sbfExpenses3","[chrExpenseType] ='Greencard
Payment'")

I don't think I can use DSum or Where in this case. Is there a way to
do a "selective" total via an expression? Many thanks. Dave

 

[Steve Schapel]

Dave,

Well, you could use DCount, along the lines that you have, but you would
need to add another segment to the where condition of the function to
specify the employee. Possibly something like this...
=DSum("[curExpenseAmount]","sbfExpenses3","[chrExpenseType]
='Greencard Payment' And [EmployeeID]=" & [EmployeeID])

But it is probably easier to use one of these options in the subform's
footer...

=Sum(IIf([chrExpenseType]='Greencard Payment',[curExpenseAmount],0))
=-Sum([curExpenseAmount]*([chrExpenseType]='Greencard Payment'))