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
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