the expression is typed in incorrectly or is too complex.... (1 Viewer)

[Misiek]

Hello all,
After couple of weeks of using the DB one of the reports which load up automatically when main form opens, stopped working, and gives an error message:

the expression is typed in incorrectly or is too complex....

report has 13 txtboxes, in every single one of them ive placed:

=sum([1])

for each month, obviously the number changes, 1, 2, 3, 4....

this in my query:

TRANSFORM sum(iif(tTaskDueDate>=tTaskComplDate and tTaskTypeFK=2,1,0))/sum(iif(tTaskTypeFK=2,1,0)) AS PercPREV
SELECT month(tTaskDueDate) AS PREVmonth, sum(iif(tTaskTypeFK=2,1,0)) AS CountPREV, sum(iif(tTaskDueDate>=tTaskComplDate and tTaskTypeFK=2,1,0)) AS onTimePREV, sum(iif(tTaskDueDate>=tTaskComplDate and tTaskTypeFK=2,1,0))/sum(iif(tTaskTypeFK=2,1,0)) AS dividePREV
FROM Q_ALL_task
WHERE qaDeptFK=Forms!F_CompLvl.cboDeptStats OR Forms!F_CompLvl!cboDeptStats IS NULL
GROUP BY month(tTaskDueDate)
PIVOT month([tTaskDueDate])
IN (1,2,3,4,5,6,7,8,9,10,11,12);

I have 7 of those report, all bases on separate transformed query, but all those queries are based on 2 main ones.

Found a hint on the web, instead of having 6 separate reports on 1 form, replace it with a main report and subreports in it. I have done it but still exactly same error.

The query it self, load up without any error, and gives correct calculations.
The report can load up without error if i leave data in DETAIL section, but I don't want it there, i just need summary, the calculations.

Any advice on this issue please.

 

[JHB]

..
The report can load up without error if i leave data in DETAIL section, but I don't want it there, i just need summary, the calculations.

Any advice on this issue please.

Then make data in the DETAIL section invisible, by setting each control's visible property to "No".

 

[Misiek]

I was considering this option as my next step.
But then when I actually tried it, it doesn't work.
As soon as I add anything to Header or Footer section, it comes with the same error.

 

[Misiek]

right,
I found something really strange....
"tTaskDueDate" - basically a user chooses a due date for a task, and then upon completion, user enter "tTaskComplDate", and then the query I've attached code from earlier, works out if the task was done before of after due date, to rate it ON TIME or not. anyway...
User have added a record, with due date in january, then another record with date in march, and then another record with date in ferbuary..., and this gives the error,
It looks like the dates must be ascending... why?
if i change date of the one in march to something in january or ferbruary (earlier date than the last entry,) everything works ok, no errors...

I really don't get it, where Could i have make a mistake while creating this DB for this to happen.
Would any query with ascending filtering of this date affect it?

 

[JHB]

Are you able to post your database with some sample data, (zip it) + name of the report in which you've the problem?

 

[Misiek]

1. please see the attached file. Open Db,
2. this is a split DB, you will most likely have to use the Linked Table Manager to get it work in first place.
3. F_CompLvl form will open automatically
4. I made a comment in red with a line pointing where the report is placed on the form.
5. on top on the screen, you will see “LOGIN", select No3
6. enter password: 1
7. on top, click "OPEN QAs" button.
8. another from will open, the problem is with the very first record, No4.
9. click on the “ACTIONS” tab in the middle of the screen
10. scroll the records, and observe “DUE DATE” as you do so.
11. 16-jan, 31-jan, 31-jan, 31-mar, 28-feb
12. if you change date of the one with February, to anything in march, the problem from home screen will disappear. the same if you change date of the March one, to be after the February, it will work ok.
13. I dont understand whats causing it.

Thank you

 

[JHB]

The problem is that there calculations in the crosstab query "SQLstats_T_prev", which is divided by zero. It is for month = 03 (tTaskDueDate = 31-03-2015), where you only entry and the tTaskCompleted = false, so here the value returned for the divisor is = 0.

 

[Misiek]

But the query itself doesn't give an error, just returns 0.
So how can I go around it?
I'm basically trying to get a percentage of on time completed tasks for each month.
Thanks.

 

[JHB]

But the query itself doesn't give an error, just returns 0.

Not quiet correct, if you change it to a Select query, you'll see it.

So how can I go around it?
I'm basically trying to get a percentage of on time completed tasks for each month.
Thanks.

Can't you only select data where tTaskTypeFK=2, then you could also avoid some of the IiF's.

 

[Misiek]

Selecting tTaskTypeFk =2 ... What about task completed on time, how will I be able to verify this?
Thanks.

 

[JHB]

Selecting tTaskTypeFk =2 ... What about task completed on time, how will I be able to verify this?
Thanks.

I think you should tell me, it is your data. You should know what is possible or not, maybe writing down the criteria/conditions will bring some light, (paper is not to be despised for that)!

 

[Misiek]

Still struggling here,
How do you calculate completion in your databases?

I imagine it this way:
CompletedRecords / TotalNumberOfRecords = Completion
2/10=0.2
0.2=20%

Thank you

 

[Misiek]

Ha! found the solution, Had to reverse the calculation
completed*100 and the divide by total

TRANSFORM sum(iif(tTaskDueDate>=tTaskComplDate and tTaskTypeFK=2,1,0))*100/sum(iif(tTaskTypeFK=2,1,0)) AS PercPREV
SELECT month(tTaskDueDate) AS PREVmonth, sum(iif(tTaskTypeFK=2,1,0)) AS CountPREV, sum(iif(tTaskDueDate>=tTaskComplDate and tTaskTypeFK=2,1,0))*100 AS onTimePREV, sum(iif(tTaskDueDate>=tTaskComplDate and tTaskTypeFK=2,1,0))/sum(iif(tTaskTypeFK=2,1,0)) AS dividePREV