Sum if issue (1 Viewer)

I'm working on a report for two sets of data. This report essentially gives me the total count of cases in a period of time.

The top portion of the report tells me how many total cases to date for the last 3 years. That information is coming up as expected and giving me the numbers i'm looking for using the following syntax.

=Sum(IIf([CaseCounts]![CaseNumber] Like "BP*" And [CaseCounts]![CaseDate] Between DateSerial(Year(Date())-2,1,1) And DateAdd("yyyy",-2,[Forms]![weekqry]![YTDEnd]),1))

This particular syntax is giving me the total number of cases from 2 years from "Date()". In this case 2016, with case numbers starting with "BP" and the end date is a field on a form filled out by the user running the report.

The part i'm having with is my second part which is just a slight difference from the original syntax.

What I am trying to do is get the number of cases whose case numbers start with bp, are in the year "2 years ago, and week number x again from a field on a form which is auomatically populated based on a chosen date on the same field.

Can someone help me out with the syntax. What i'm thinking is =sum(iif([casecounts]![Casenumber] like "bp*" And year([casedate]=year(date()-2)) And Week X

AND Week([casedate])= [Week X]

Gasman,

Can you tell based on the syntax noted that i'm writing the syntax correctly?

Here is the complete syntax that I used.

=sum(iif([casecounts]![Casenumber] like "bp*" And year( [CaseCounts]![CaseDate] =year(date()-2)) And Week([CaseCounts]![CaseDate]= Forms![weekqry]![weekNumber]))))

When I run it I get an error indicating the expression you entered has a function containing the wrong number of arguments.

Well looking at your first formula (which you say works?) I just worked off that.
An IIF function should have a value if true and a value if false, so i would expect the field that you are trying to Sum if True, and 0 if not. ?

The same would apply to your second formula.

Could you not simplify this by putting the criteria in the source of the report.?

Anyway check out the syntax for IIF

https://www.excelfunctions.net/vba-iif-function.html

HTH

You will need to match the brackets, but after the test, you need a value for true and a value for false, which would probably be zero (0)

Look at the link I posted.

Possibly

which makes me wonder how your first formula works?

unclefink said:

Here is the complete syntax that I used.

=sum(iif([casecounts]![Casenumber] like "bp*" And year( [CaseCounts]![CaseDate] =year(date()-2)) And Week([CaseCounts]![CaseDate]= Forms![weekqry]![weekNumber]))))

When I run it I get an error indicating the expression you entered has a function containing the wrong number of arguments.

Click to expand...

Ok, I see what you are talking about.

I used the following syntax which worked perfectly.

=Sum(IIf([casecounts]![Casenumber] Like "bp*" And Year([CaseCounts]![CaseDate])=Year(Date())-1 And [CaseCounts]![Week Number]=[Forms]![weekqry]![weekNumber],1,0))

In the ending 1,0: the "1" is the multiplier for every record that matches the criteria and the "0" is the result if false.

Thank you very much Gasman. Very much appreciate your assistance.