Summing in a report using iif and multiple criteria

[trishcollins]

I have a table which is a snapshot in time for monthly cost of a service. Each service has a start date, and if the service has been canceled, it has an end date. If the service has not been canceled, the end date field is empty.

I have a form which allows the user to determine what "year" they want the report to select and it is passed to the query and is applied as a filter. [Forms]![NSB Dynadocs Inventory Report Query Form]![Fiscal_Year] ie 2011

I created a query for the report with four new fields where I extract the month and year of both the start and end date fields so I can do the comparisons to the fiscal year choosen by the user in the form.
[FiscalYearEndDate] ie 2011
[FiscalYearEndMonth] ie 4
[FiscalYearStartDate] ie 2011
[FiscalYearStartMonth] ie 4

The query runs without a problem, and I can see the proper data in those newly created fields.

I have created a summary report that will give a one year view of the data broken down by month, based on the start date and end date (if applicable). If fiscal year input by the user from the form is greater than the start date year of the service, OR if the start date year is the same as the input in the form, and the column month (Jan=1, Feb=2, Mar=3, etc.), ) is greater than or equal the start date month of the service, then include the Monthly Recurring Cost [MRC] in the sum. Keeping in mind, if the service also has an end date, and only if the end date year is equal to the fiscal year input into the form AND the end date month is greater than or equal to the column month (Jan=1, Feb=2, Mar=3, etc.), then include the [MRC] in the sum. Obviously, if the end date year is less than the fiscal year the user is requesting, the cost should not be included in the sum.

I have tried both of the following in the report and I get errors on both -- "you have entered a comma without a preceding value or identifier"

=Sum(IIf((isnull([FiscalYearEndDate]) AND [FiscalYearStartDate] <[Forms]![NSB Dynadocs Inventory Report Query Form]![Fiscal_Year]) Or (isnull([FiscalYearEndDate]) AND [FiscalYearStartDate] = [Forms]![NSB Dynadocs Inventory Report Query Form]![Fiscal_Year] AND [FiscalYearStartMonth]=<1) Or ([FiscalYearEndDate] = [Forms]![NSB Dynadocs Inventory Report Query Form]![Fiscal_Year] AND [FiscalYearEndMonth] =>1),[MRC],0))

=Sum(IIf(isnull([FiscalYearEndDate]) And [FiscalYearStartDate]<[Forms]![NSB Dynadocs Inventory Report Query Form]![Fiscal_Year], [MRC],iif(isnull([FiscalYearEndDate]) AND [FiscalYearStartDate] = [Forms]![NSB Dynadocs Inventory Report Query Form]![Fiscal_Year] AND [FiscalYearStartMonth]=<1,[MRC],iif([FiscalYearEndDate] = [Forms]![NSB Dynadocs Inventory Report Query Form]![Fiscal_Year] AND [FiscalYearEndMonth] =>1,[MRC],0))))
FYI - this formula is specific for Jan, as the month number is 1, the formula would be changed as it moves across the columns

I am also not sure how to evaluate an empty date field. Should it be Isempty([FiscalYearEndDate]) or isnull([FiscalYearEndDate])

Any suggestions?
Trish

 

[PNGBill]

When posting code it is best to use the hash symbols available between the Qute and php buttons of your post form.

like this is

Have you tried to simplify the code and build up the complexity resolving any issues as they occur ?

Sometimes, I use an impossible date in the data source rather then having to deal with nulls.
eg our business began in 1999 so any transaction date earlier then this means it should be excluded. Not always possible but sometimes it does make life easier.

 

[PNGBill]

http://office.microsoft.com/en-ca/a...s-that-check-for-null-values-HP001099035.aspx

this link has info on null dates

 

[trishcollins]

Have you tried to simplify the code and build up the complexity resolving any issues as they occur ?

I have tried, but not every combination. I was thinking the "AND" and "OR" were causing the problem, so I did the nested iif to see if that would work.

I will work my way through the various combinations to see if I can get any of the expression to work and/or at what point it fails.

 

[trishcollins]

this link has info on null dates

Thanks. It just dawned on me that when doing the comparison, the year and date which I have extracted, are now just numbers and can be treated that way. I suppose I need to use IsNull because the field we be empty, not equal to 0.

Trish

 

[PNGBill]

Nested Iif may be easier to follow and debug.

 

[trishcollins]

I just figured it out the expression problem. It doesn't like => or =< it wants <= or >= in that order Rookie move. Now I can enter the formula but get #error on the report. Not sure why but I am at least over the first hurdle. I will try breaking down the formula again and actually running the report and see if I get any errors on each individual segment.

 

[trishcollins]

I just broke it down into three expressions and then added them together. Not pretty, but it works:

=Sum(IIf(isnull([FiscalYearEndDate]) And [FiscalYearStartDate]<[Forms]![NSB Dynadocs Inventory Report Query Form]![Fiscal_Year], [MRC], 0))+
Sum(IIf(isnull([FiscalYearEndDate]) AND [FiscalYearStartDate] = [Forms]![NSB Dynadocs Inventory Report Query Form]![Fiscal_Year] AND [FiscalYearStartMonth] <= 1, [MRC], 0))+
Sum(IIf([FiscalYearEndDate] = [Forms]![NSB Dynadocs Inventory Report Query Form]![Fiscal_Year] AND [FiscalYearEndMonth] >= 1, [MRC], 0))

Thanks.... Trish

 

[PNGBill]

Good news