Error from simple statement

[kblehman]

Okay, now I'm suspecting there's more to this than an overly complicated control source statement....

My summary report contains multiple objects that calculate totals based on a statement in their control source. The report ran fine for a few weeks but all of a sudden it started producing this error:

"The expression is typed incorrectly, or it is too complex 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."

I thought perhaps that 12 lengthy control source statements were simply too much for Access to handle, but then I did some testing and eventually removed all but this control source statement: =Sum([NotifType]=9)

That's about as simple as it gets, but when I try and run the report I still get the error. Ggggrrrrrrrrr....

Can anyone tell me what causes this annoying error and how I can get rid of it? (I already compiled.) What little Access quirk am I running into? I will gladly zip the file and post if necessary.

Thank you.

 

[boblarson]

=Sum([NotifType]=9)

is not a valid formula.

=Sum(IIf([NotifType]=9,1,0))

would be a valid type or

=Sum(IIf([NotifType]=9,[NotifType],0))

would also be valid.

 

[pbaldy]

I'd say post the file. I think the formula is valid, Bob. It's basically summing the result of a boolean ("[NotifType]=9" will evaluate to either true or false). Since it was working and then stopped, my first guess is a data problem.

Edit: obviously the sum would be a negative number, which I didn't mention because I saw the OP had wrapped it in an Abs() function in another thread.

 

[kblehman]

Bob & pbaldy,
Thank you for your willingness to help.

Here are the 13 control source statements used to produce the report results. If you're willing to take a look at these statements and help me correct any syntax errors I've created, I would certainly appreciate it.

I should mention that since yesterday I've been trying to figure out which of these may be causing problems. By process of elimination, the 3rd one is a culprit. When I remove all the other fields from the report but the one with this statement, I still get the error message. There's at least one other one too, but I haven't narrowed it down yet.

I'll post the entire db in a subsequent reply.

Statements:

=Sum(Abs(Year([RecDate])=Year(Date()) And ([NotifType]=9)))

=Sum(Abs(([RecDate] Between [Forms]![frmRptCriteria]![txtBeginDate] And [Forms]![frmRptCriteria]![txtEndDate]) And [NotifType]=9))

=Sum(Abs([RecDate] Between ([Forms]![frmRptCriteria]![txtBeginDate]-365) And ([Forms]![frmRptCriteria]![txtEndDate]-365) And [NotifType]=9))

=Sum(Abs(Year([RecDate])=(Year(Date())) And ([NotifType]=9) And ([IncidType]=6)))

=Sum(Abs([RecDate] Between [Forms]![frmRptCriteria]![txtBeginDate] And [Forms]![frmRptCriteria]![txtEndDate] And ([NotifType]=9) And ([IncidType]=6)))

=Sum(Abs(Year([RecDate])=(Year(Date())) And ([NotifType]=9) And ([IncidType]=5)))

=Sum(Abs([RecDate] Between [Forms]![frmRptCriteria]![txtBeginDate] And [Forms]![frmRptCriteria]![txtEndDate] And ([NotifType]=9) And ([IncidType]=5)))

=Sum(Abs(Year([RecDate])=(Year(Date())) And ([RespType]=26)))

=Sum(Abs([RecDate] Between [Forms]![frmRptCriteria]![txtBeginDate] And [Forms]![frmRptCriteria]![txtEndDate] And [RespType]=26))

=Sum(Abs(Year([RecDate])=(Year(Date())) And (([ServDelVeh1]=42) Or ([ServDelVeh2]=42) Or ([ServDelVeh3]=42))))

=Sum(Abs([RecDate] Between [Forms]![frmRptCriteria]![txtBeginDate] And [Forms]![frmRptCriteria]![txtEndDate] And (([ServDelVeh1]=42) Or ([ServDelVeh2]=42) Or ([ServDelVeh3]=42))))

=Sum(Abs(Year([RecDate])=(Year(Date())) And ([IncidType]=5 Or ([IncidType]=6))))

=Sum(Abs([RecDate] Between [Forms]![frmRptCriteria]![txtBeginDate] And [Forms]![frmRptCriteria]![txtEndDate] And ([IncidType]=5 Or ([IncidType]=6))))

=Sum(Abs([RecDate] Between ([Forms]![frmRptCriteria]![txtBeginDate]-365) And ([Forms]![frmRptCriteria]![txtEndDate]-365) And ([IncidType]=5 Or ([IncidType]=6))))

Thanks again!

 

[kblehman]

It is the 3rd and the last statements that are causing the problem:


=Sum(Abs([RecDate] Between ([Forms]![frmRptCriteria]![txtBeginDate]-365) And ([Forms]![frmRptCriteria]![txtEndDate]-365) And [NotifType]=9))



=Sum(Abs([RecDate] Between ([Forms]![frmRptCriteria]![txtBeginDate]-365) And ([Forms]![frmRptCriteria]![txtEndDate]-365) And ([IncidType]=5 Or ([IncidType]=6))))

 

[boblarson]

Try this instead:

=Sum(Abs([RecDate] Between DateAdd("d",-365,[Forms]![frmRptCriteria]![txtBeginDate]) And DateAdd("d", -365, [Forms]![frmRptCriteria]![txtEndDate]) And [NotifType]=9))



=Sum(Abs([RecDate] Between DateAdd("d", -365, [Forms]![frmRptCriteria]![txtBeginDate]) And DateAdd("d", -365, [Forms]![frmRptCriteria]![txtEndDate]) And ([IncidType]=5 Or ([IncidType]=6))))

 

[kblehman]

Bob & Pbaldy,
Attached is a zip file of the database.

Report = rptActivitySummary

frmRptCriteria contains begin & end date fields that are used by the report for its date range. If you don't open frmRptCriteria and add dates, you'll have to manually enter the date range each time you try and run rptActivitySummary.

Thank you for your help,
Kerry

 

[boblarson]

Need you to repost. Create a copy of your database and delete the linked table and import it in, or include the db with the table, so that we have it. The report is useless without it and I can't see anything.

 

[kblehman]

Oops, I forgot I had linked the data table. I apologize.

Try this one.

 

[pbaldy]

The math is apparently too much for it. This works:

=Sum(Abs([RecDate] Between DateAdd("d",-365,[Forms]![frmRptCriteria]![txtBeginDate]) And DateAdd("d",-365,[Forms]![frmRptCriteria]![txtEndDate]) And [NotifType]=9))

 

[kblehman]

Try this instead:

=Sum(Abs([RecDate] Between DateAdd("d",-365,[Forms]![frmRptCriteria]![txtBeginDate]) And DateAdd("d", -365, [Forms]![frmRptCriteria]![txtEndDate]) And [NotifType]=9))



=Sum(Abs([RecDate] Between DateAdd("d", -365, [Forms]![frmRptCriteria]![txtBeginDate]) And DateAdd("d", -365, [Forms]![frmRptCriteria]![txtEndDate]) And ([IncidType]=5 Or ([IncidType]=6))))

Both statements work perfectly. Thank you, Bob!