ms-access

Calculating Year-to-date income and expense

The following expression works in my query based finance report (calculated text box) for summing income for current month with a similar one for expense

=Sum(IIf([be,bi,e,i]="i",[Inc £],0))

however I would like to be able to sum current month plus previous months (i.e year-to-date) am trying the following

 =Sum(IIf([EntryDate] >=DateSerial(Year(Date()),2,1) And <=Now()),(([be,bi,e,i]="i"),[Inc £],0))

but get the following error message - The expression you entered contains invalid syntax. You may have entered a comma without a preceding value or identifier.

I would be grateful for any help with this problem.

Thanks in advance.

Have changed my expression to

=Sum(IIf([EntryDate] >=DateSerial(Year(Date()),2,1) And [EntryDate] <=Now()),(([be,bi,e,i]="i"),[Inc £],0))

but am now getting error - The expression you entered has a function containing the wrong number of arguments.

I think I need more brackets but not sure where they should go.

Thanks

Solution

  • You are adding date range criteria so use AND operator with existing condition.

    =Sum(IIf([EntryDate] >=DateSerial(Year(Date()),2,1) AND [EntryDate] <=Date() AND [be,bi,e,i]="i", [Inc £], 0))

    And if there are no records with future dates, don't bother with end date.

    or

    =Sum(IIf([EntryDate] BETWEEN DateSerial(Year(Date()),2,1) AND Date() AND [be,bi,e,i]="i", [Inc £], 0))