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

 

[PHV]

You don't need the WHERE clause in the crosstab query as qryCountAbsJoin already filters the date.

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

 

[kmkland]

PH,

I removed the WHERE clause, but yet, I still get the same error message as stated earlier in my thread....

Here is the revised statement:

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
GROUP BY [qryCountAbsOuterJoin2].[Date_of_Absence]
PIVOT [qryCountAbsOuterJoin2].[Absence Code];

Thanks so much for your help, PH!!!

Rgds,
kmkland

 

[PHV]

Have you tried to put the PARAMETERS instruction in the 2 others queries ?

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

 

[kmkland]

PH,

I tried that, and yet, I still get the same message.

Rgds,
Kmkland

 

[lameid]

Try using dots (.) instead of bangs (!) inside your queries and aliasing anything with a delimeter (# is a date delimeter) in your base query to something that does not have that in the name.

I.e.

SELECT [tblReasonsForAbsences].

 & ' ' & [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];

Of course you should add the parameters back in.

If that doesn't work, try copying the table and removing the #'s from the copy and running the queries based off that.  At least you'll know if it is the source of the problem.

 

[kmkland]

Lameid,
This still does not work! I am at such a loss!!!


Rgds,
Kmkland

 

[PHV]

As qryCountAbsJoin uses only INNER JOINs you may simply it's WHERE clause:
WHERE (tblAbsences.Date_of_Absence Between [Forms]![frmParameters]![StartDate] And [Forms]![frmParameters]![EndDate]) AND (tblHireDates.Date_of_Separation Is Null)

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

 

[lameid]

In an attempt to isolate the problem....

Change your first query to a make table query and make a table.

Next make an equivalent crostab query based off that and see if it runs (I like copying SQL statements over to Wordpad and doing find and replaces to make a query quickly).

If it does it must be something in the base query if not, then data becomes a suspect. Like does [qryCountAbsOuterJoin2].[Absence Code] contain Nulls or Zero lenght strings?

 

[lameid]

PHV and I cross posted... Try his suggestion first. Good catch BTW.

 

[dhookom]

I don't like

CDate(Format([qryCountAbsJoin].[Date_of_Absence],"mm/\1/yyyy")) AS Date_of_Absence

Your alias is the same as a column/field name. Plus you change a date to a string and then back to a date. Try:

DateAdd("d",-(Day([qryCountAbsJoin].[Date_of_Absence])-1),[qryCountAbsJoin].[Date_of_Absence]) AS Month_of_Absence

You will need to change to Month_Of_Absense in later queries.

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]

 

[kmkland]

PH,

As qryCountAbsJoin uses only INNER JOINs you may simply it's WHERE clause:

What was left out in that statement?

Lameid,

I made a table from my query. It worked just fine.
I concatenated 2 fields to make Absence Code. There are no null values. I'm not quite sure how I would go about determining zero-length string.

Dhookom,

I made the changes that you suggested, but yet, I'm still getting the same error message.

Thank you all so much for your help on this!!....
Any more suggestions!?!!?!?!

Rgds,
Kmkland

 

[PHV]

Sorry for the typo: you may simplify it's WHERE clause

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

 

[lameid]

= ""

The above is what you would put in the QBE to test for a 0 length string... A value of just double quotes. It may seem odd but it is different from a null.

Did you make a crosstab of your resultant table? Did it work? If not does removing the hashes (#) help?

It also might not like pivoting on a concatenated value.
One solution would be to make a table that has 3 fields.

, [Reasons_for_Absences], [Absence Code]
You could create a primary key over the first 2 fields.  Next you could populate the table with values including the combined absence code.  Add this table to your query joining on the first 2 fields and then pivot off the third field.

 

[kmkland]

I solved the problem with the error message stating that the expression is too complex; I removed the following date format from the query upon which my crosstab query was based:

CDate(Format([qryCountAbsJoin].[Date_of_Absence],"mm/\1/yyyy")) AS Date_of_Absence

Unfortunately, I need this format to combine the dates in my report into 1 row for each month instead of multiple rows for each month.
i.e., I need this... instead of...
Jan 2005 Jan 2005
Mar 2005 Jan 2005
Jul 2005 Mar 2005
Mar 2005

I've tried putting the expression into the crosstab query, but that doesn't help either. I'm not sure what I should do, or where I should put this expression if I'm even supposed to use this expression!

Thanks in advance for all your help!

Rgds,
Kmkland

 

[dhookom]

I still think your expression is overly complex when you take a date, convert it to a string with a hard-coded "1", and then convert it back to a date.

If all you want to do is group by month, consider two columns:
Yr:Year(Date_Of_Absense)
Mth:Month(Date_Of_Absense)
or
YrMth: Year(Date_Of_Absense)*100+Month(Date_Of_Absense)

The Date_of_Absense is never Null is it?


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]

 

[kmkland]

DHOOKOM,

I have a main table with employee information and EmpFileNo as the primary key. This is linked to another table which details the employees' absences. There is only an entry under Date_of_Absence if the employee has been absent. The relationship is 1 (Main) to Many (Absences). I need to generate a report which shows the month/year of absence as a row heading, the codes for the column headings & the count, for the crosstab.

I am not sure how I approach finding 0 length strings in my queries. Where exactly do I put the =""? Into which query?

Here is what I am working with now...
Base query:

SELECT tblEmpInfo.EmpFileNo, tblEmpInfo.DeptCode, tblAbsences.Date_of_Absence, tblAbsences.Code
FROM (tblEmpInfo INNER JOIN tblHireDates ON tblEmpInfo.EmpFileNo = tblHireDates.EmpFileNo) INNER JOIN tblAbsences ON tblEmpInfo.EmpFileNo = tblAbsences.EmpFileNo
WHERE (((tblHireDates.Date_of_Separation) Is Null))
ORDER BY tblAbsences.Date_of_Absence;

Join query:

SELECT tblReasonsForAbsences.Code, Count(qryCountAbsJoin.EmpFileNo) AS CountOfEmpFileNo, qryCountAbsJoin.Date_of_Absence
FROM tblReasonsForAbsences LEFT JOIN qryCountAbsJoin ON tblReasonsForAbsences.Code = qryCountAbsJoin.Code
GROUP BY tblReasonsForAbsences.Code, qryCountAbsJoin.Date_of_Absence
ORDER BY qryCountAbsJoin.Date_of_Absence;

Crosstab query:

TRANSFORM Sum(qryCountAbsOuterJoin2.CountOfEmpFileNo) AS SumOfCountOfEmpFileNo
SELECT qryCountAbsOuterJoin2.Date_of_Absence, Sum(qryCountAbsOuterJoin2.CountOfEmpFileNo) AS [Total Of CountOfEmpFileNo]
FROM qryCountAbsOuterJoin2
GROUP BY qryCountAbsOuterJoin2.Date_of_Absence
PIVOT qryCountAbsOuterJoin2.Code;

Within my report, I have the control source for Date_of_Absence field as:

=Format$([Date_of_Absence]-0,"mmm yyyy",0,0)

As far as adding ="" to my query/queries, I'm not sure which query I would add this!?!?? I have tried every single suggestion except for this one.

Thanks in advance for all suggestions!!!



Rgds,
Kmkland

 

[kmkland]

Just thought that I would include this bit of information:

I have a similar crosstab query where I was able to accomplish what I needed - displaying & grouping the month/year as row headings on my report.

Base query:

SELECT tblEmpInfo.EmpFileNo, CDate(Format(tblAbsences.Date_of_Absence,"mm/\1/yyyy")) AS Date_of_Absence, tblAbsences.Code
FROM tblEmpInfo INNER JOIN (tblAbsences INNER JOIN tblReasonsForAbsences ON tblAbsences.Code = tblReasonsForAbsences.Code) ON tblEmpInfo.EmpFileNo = tblAbsences.EmpFileNo
WHERE (((tblAbsences.Code)="21" Or (tblAbsences.Code)="22" Or (tblAbsences.Code)="23"))
ORDER BY tblAbsences.Date_of_Absence;

Crosstab query:

PARAMETERS [Forms].[frmParameters].[txtStartDate] DateTime, [Forms].[frmParameters].[txtEndDate] DateTime;
TRANSFORM Count(qryTtlOccur_Count.EmpFileNo) AS CountOfEmpFileNo
SELECT qryTtlOccur_Count.Date_of_Absence, Count(qryTtlOccur_Count.EmpFileNo) AS [Total Of EmpFileNo]
FROM qryTtlOccur_Count
WHERE (((qryTtlOccur_Count.Date_of_Absence) Between [Forms].[frmParameters].[txtStartDate] And [Forms].[frmParameters].[txtEndDate]))
GROUP BY qryTtlOccur_Count.Date_of_Absence
PIVOT qryTtlOccur_Count.Code;

Now, the report for these queries works just fine. I'm not sure where I am going wrong with the other!!!

Rgds,
Kmkland

 

[lameid]

If tblAbsences.Date_of_Absence has a null for your criteria, you will have problems because the functions will not handle it properly.

I suspect that the field should always contain a value so you may want to resolve all the nulls and then set the required property of the field to yes so that noone may leave it blank (Null) any more.

An alternative would be to pick a date for nulls like 1/1/1900 to substitue for nulls.

tblAbsences.Date_of_Absence then becomes
NZ(tblAbsences.Date_of_Absence, #1/1/1900#)

If you do this and still have problems, use dhookom's suggestion of
YrMth: Year(Date_Of_Absense)*100+Month(Date_Of_Absense)

But instead use substite with NZ that I gave you...

YrMth: Year(NZ(tblAbsences.Date_of_Absence, #1/1/1900#))*100+Month(NZ(tblAbsences.Date_of_Absence, #1/1/1900#))

That will yield an integer or long integer fromated yyyymm that you can pivot on and will use 1/1/1900 if the date is null.

 

[kmkland]

In the base query, I made the following change to the date format:

SELECT tblEmpInfo.EmpFileNo, tblEmpInfo.DeptCode, tblAbsences.Date_of_Absence, tblAbsences.Code
FROM (tblEmpInfo INNER JOIN tblHireDates ON tblEmpInfo.EmpFileNo = tblHireDates.EmpFileNo) INNER JOIN tblAbsences ON tblEmpInfo.EmpFileNo = tblAbsences.EmpFileNo
WHERE (((tblAbsences.Date_of_Absence)=NZ([Date_of_Absence],#1/1/1900#)) AND ((tblHireDates.Date_of_Separation) Is Null))
ORDER BY tblAbsences.Date_of_Absence;

In the outer join, I made the date format change you suggested:

PARAMETERS [Forms].[frmParameters].[StartDate] DateTime, [Forms].[frmParameters].[EndDate] DateTime;
SELECT tblReasonsForAbsences.Code, Count(qryCountAbsJoin.EmpFileNo) AS CountOfEmpFileNo, qryCountAbsJoin.Date_of_Absence, Year(NZ(qryCountAbsJoin.Date_of_Absence,#1/1/1900#))*100+Month(NZ(qryCountAbsJoin.Date_of_Absence,#1/1/1900#)) AS YrMth
FROM tblReasonsForAbsences LEFT JOIN qryCountAbsJoin ON tblReasonsForAbsences.Code = qryCountAbsJoin.Code
WHERE (((qryCountAbsJoin.Date_of_Absence) Between [Forms].[frmParameters].[StartDate] And [Forms].[frmParameters].[EndDate]))
GROUP BY tblReasonsForAbsences.Code, qryCountAbsJoin.Date_of_Absence, Year(NZ(qryCountAbsJoin.Date_of_Absence,#1/1/1900#))*100+Month(NZ(qryCountAbsJoin.Date_of_Absence,#1/1/1900#))
ORDER BY qryCountAbsJoin.Date_of_Absence;

...but yet in my report, I am getting a message that states that MS does not recognise " as a valid field name or expression.
Date_of_Absence is now required in the field properties of tblAbsences.

Is there a completely different approach to this that I may possibly take? (i.e., table modification) At this point, I think I am willing to try just about anything to solve this problem. I cannot duplicate my DB until this issue is resolved, and I wish that I knew how to solve my own problem. It seems though that several heads are always better than one!

Thanks in advance for your continued help on this matter.

Rgds,
Kmkland