Report : Problem using Count(IIf ...

[eko99312]

I made a report concept from a simple query that contain as follow

SELECT [Table1].[Date], [Table1].Reason
FROM [Table1]
GROUP BY [Table1].[Date], [Table1].Reason
HAVING ((([Table1].[Date])>=[FirstDate] And ([Table1].[Date])<=[LastDate]) AND (([Table1].Reason) Is Not Null));

When I click the view button, the query shows the date and the reasons record. Well, I assume the query works well.

Now, I want to make a report using that query, but is not a regular kind of report, I wanted to be like this :

No   Date         On Leave     Sick      Present      Business Trip
1    October'09   1            4         50           5
2    November'09  2            10        48           0

To get the Reason value, I enter this to it control source =Count(IIf([Reason]="On Leave",0)), that also for all 4 kind of reason type.
The strange is for the other report using this model I have no problem, but somehow for this one it came with warning like this :
"
This 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."

Please help me on this one. I try to remake the report that's work but still not working for this 2 table.

[MikeTheBike]

Hi

To achieve what you have illustrated I think something like this should do it

SELECT Format([Table1].[Date],"mmm yy") as [Date], Sum(IIF([Reason]="On Leave",1,0) as [On Leave], Sum(IIF([Reason]="Sick",1,0) as [Sick], Sum(IIF([Reason]="Present",1,0) as [Present], Sum(IIF([Reason]="Business Trip",1,0) as [Business Trip]
FROM [Table1]
GROUP BY Format([Table1].[Date],"mmm yy")
HAVING ((([Table1].[Date])>=[FirstDate] And ([Table1].[Date])<=[LastDate]) AND (([Table1].Reason) Is Not Null));

??

I would suggest (if you havn't done it this way) is to have a Lookup table containing the 'Reasons' for the absences and create a relationship with Table1 based on the Reason_ID (long integer) contained in the new table. This would change the query to look something like this.

SELECT Format([Table1].[Date],"mmm yy") as [Date], Sum(IIF([Reason_ID]=1,1,0) as [On Leave], Sum(IIF([Reason_ID]=2,1,0) as [Sick], Sum(IIF([Reason_ID]=3,1,0) as [Present], Sum(IIF([Reason_ID]=4,1,0) as [Business Trip]
FROM [Table1]
GROUP BY Format([Table1].[Date],"mmm yy")
HAVING ((([Table1].[Date])>=[FirstDate] And ([Table1].[Date])<=[LastDate]) AND (([Table1].Reason_D) Is Not Null));

The other thing is that it is a VERY bad idea to use "Date" as a field name (or any other keyword). Believe me; this advice is based on experience (having ignored vague recollection of this advice previously!!!).


MTB