DSUM Issue on Form

[psullivan348]

I can not get the AND statement to work with DSUM

Here is my scenerio:

table: tblActualExpense

Fields Involved:

SchoolNumber (Primary Key) Combo box looks up from tblSchool
FiscalYear (Primary Key) Long Integer Number
QtrNumber (Primary Key) Long Integer Number
TotalTeacherCompensation Currency

form: frmSchoolActualExpenses

SchoolNumber (Unbound Combo box) lookup

subform: subSchoolActualExpenses

FiscalYear (Unbound Combo Box) lookup
QtrNumber (Unbound Combo Box) lookup
TeacherCompensation

Okay, now I have that all right. Everything comes from where it is supposed to and life is good, until....

I need to have another column of YTD information that needs filter by school and total all the Fiscal year for the selected year.

I have tried many, many things but got half way there with DSUM.

This is what I have:

=nz(DSUM(TotalTeacherCompensation","tblActualExpenses", "FiscalYear= &[FiscalYearHere] AND SchoolNumber= &Forms![subSchoolActualExpenses]![FiscalYearHere]&")

when I break them apart and link to one or the other it works fine. It is a issue with the AND and I just want to be done with this!!!!!! Or maybe it has to do with one being on the main form and the other being on the sub form. I have no idea but I sure want to.

If there is a easier way to do this, please help or point me into the right direction.

Thanks very much for whatever help I can get!!!!!

 

[Rich]

Why do you store fiscal year and qtr number as separate fields and not just the actual Date?

 

[lj1602]

Hello,

It's difficult to work out exactly where you want the new field with the YTD value to show, however, just using the DSum statement from your post, the WHERE clause should read:

"FiscalYear = " & [FiscalYearHere] & " AND SchoolNumber = " & Forms!frmSchoolActualExpenses![subSchoolActualExpenses]!Form.[FiscalYearHere]

I think you're missing the parent form reference and Form property between the subform and the combo box. Make sure to take the field references outside the quotation marks.

I also think in the example that you have SchoolNumber and FiscalYear the wrong way around?
Hope this helps.