Problems with Expression in Crosstab Query

[kmkland]

I get this message...:

This expression is typed incorrectly, or it is too complex to be evaluated...Try simplifying the expression by assigning parts of the expression to variables.

...whenever I run my crosstab query:

PARAMETERS [Forms]![frmParameters]![StartDate] DateTime, [Forms]![frmParameters]![EndDate] DateTime;
TRANSFORM Count(qryCountAbsOuterJoin2.[CountOfEmpFile#]) AS [CountOfCountOfEmpFile#]
SELECT qryCountAbsOuterJoin2.Date_of_Absence, Count(qryCountAbsOuterJoin2.[CountOfEmpFile#]) AS [Total Of CountOfEmpFile#]
FROM qryCountAbsOuterJoin2
WHERE (((qryCountAbsOuterJoin2.Date_of_Absence) Between [Forms]![frmParameters]![StartDate] And [Forms]![frmParameters]![StartDate]))
GROUP BY qryCountAbsOuterJoin2.Date_of_Absence
PIVOT qryCountAbsOuterJoin2.[Absence Code];

I am not quite sure where the problem lies.

The crosstab query is based upon this query:

SELECT [tblReasonsForAbsences]![Code] & ' ' & [tblReasonsForAbsences]![Reasons_for_Absences] AS [Absence Code], Count([qryCountAbsJoin].[EmpFile#]) AS [CountOfEmpFile#], CDate(Format([qryCountAbsJoin].[Date_of_Absence],"mm/\1/yyyy")) AS Date_of_Absence
FROM tblReasonsForAbsences LEFT JOIN qryCountAbsJoin ON [tblReasonsForAbsences].[Code]=[qryCountAbsJoin].[Code]
GROUP BY [tblReasonsForAbsences]![Code] & ' ' & [tblReasonsForAbsences]![Reasons_for_Absences], [qryCountAbsJoin].[Date_of_Absence]
ORDER BY [qryCountAbsJoin].[Date_of_Absence];

...which works just fine.

This is the base query:

SELECT tblEmpInfo.[EmpFile#], tblEmpInfo.DeptCode, tblAbsences.Date_of_Absence, tblAbsences.Code
FROM (tblEmpInfo INNER JOIN tblHireDates ON tblEmpInfo.[EmpFile#] = tblHireDates.[EmpFile#]) INNER JOIN tblAbsences ON tblEmpInfo.[EmpFile#] = tblAbsences.[EmpFile#]
WHERE (((tblAbsences.Date_of_Absence) Is Null Or (tblAbsences.Date_of_Absence) Between [Forms]![frmParameters]![StartDate] And [Forms]![frmParameters]![EndDate]) AND ((tblHireDates.Date_of_Separation) Is Null))
ORDER BY tblAbsences.Date_of_Absence;

Please, any advice on this matter will be greatly appreciated!!!

Thanks in advance!

Rgds,
Kmkland

 

[lameid]

Sorry, I've been away from computers for a week... So the query runs and the report doesn't?

It sounds like you have '' enetered as either the control source of a control or have it in the grouping settings.

Remeber to only save if you mean it and repopen the report if neccessary if doing the following... If it isn't apparent, I suggest you delete all the controls save one from your report (after verifying the control source) and see if it runs. If it does, it is definitely a control issue otherwise, happy hunting. If it is a control, I usually delete groups of controls until the problem goes away and then either examine the controls or delete them individually until I find it.

 

[dhookom]

Is the Code required? If so, why would you use a LEFT JOIN? If you want to pivot on Code in your crosstab, you must filter out Null values or use Nz(tblReasonsForAbsences.Code,"N/A")

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]