Sum records entered in CY where tbl field = 6

[KerryL]

I'm having trouble with a field on a report. The field needs to show a sum of the records that have been entered in the calendar year, and also have a value of "6" in one of the table fields.

I wasn't sure how to make the report object know what year it is, so I thought of placing an invisible text box on the form, format it as yyyy and use =Date() as the control source. If that's not a good way to do it, please advise.

Report = rptActivitySummary
Table = tblWARNData (control source for report)
Form = frmReportCriteria (users enter date range, etc)
YYYY object = txtYear on frmReportCriteria
Tbl Field 1 = EntryDate (date record entered)
Tbl Field 2 = IncidType

This is my current SQL, but it's not working.

If(Year([EntryDate])=[Forms]![frmRptCriteria]![txtYear]) Then Sum(Abs([IncidType])="6")


Any advice will be greatly appreciated. TIA!

 

[PHV]

Sum(IIf(Year([EntryDate])=Year(Date()) AND [IncidType]='6',1,0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[KerryL]

Thanks PHV.
Unfortunately, I'm getting the same error with your code as I got with mine:

Extra ) in query expression

...even though there doesnt seem to be an extra one.

 

[PHV]

Could you please post the code raising the error explaining us where it is located.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[KerryL]

Here's the code I'm using in the Control Source of the report field:

=Sum(IIf(Year([EntryDate])=Year(Date()) And [IncidType]='6',1,0))

This is the error I'm getting now:

"The expression is typed incorrectly, or is too complext to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.