Group By Data Type mismatch error

[Biggles]

When I attempt to perform a group by (either select or crosstab query) on a
field calculated in a previous query, I get a "Data Type mismatch in criteria
expression" error. I am not sure why this occurs. I can display the field
fine if I don't have the total line showing in the design view. This is the
SQL I am trying to run, and various tests have limited it down to the NEXTAUD
field as the problem.

TRANSFORM Count(qryASSIGN_CYCLE.txtAENO) AS CountOftxtAENO
SELECT qryASSIGN_CYCLE.txtAENO, qryASSIGN_CYCLE.NEXTAUD
FROM qryASSIGN_CYCLE
GROUP BY qryASSIGN_CYCLE.txtAENO, qryASSIGN_CYCLE.NEXTAUD
PIVOT qryASSIGN_CYCLE.txtSCOPE_ID;

The qryASSIGN_CYCLE looks like this (with the **** added to offset the
nextaud, this query works appropriately):

SELECT qryASSIGN_CYCLE_INTERIM.txtAENO, qryASSIGN_CYCLE_INTERIM.RISK_LEVEL,
qryASSIGN_CYCLE_INTERIM.txtSCOPE_ID, qryASSIGN_CYCLE_INTERIM.LAST_AUDIT,
rtblAUDIT_FREQ.intCYCLE,
****
IIf([YNOOVERRIDE]=-1,[NEXTAUD02]+1,[NEXTAUD02]) AS NEXTAUD,
*****
[NEXTAUD]+[INTCYCLE] AS NEXTAUD1, [NEXTAUD1]+[INTCYCLE] AS NEXTAUD2,
[LAST_AUDIT]+[INTCYCLE] AS NEXTAUD01, rtblSCOPE.txtSCOPE,
IIf([nextaud01]<2006,2006,[nextaud01]) AS NEXTAUD02,
qryASSIGN_CYCLE_INTERIM.ynoOVERRIDE
FROM qryASSIGN_CYCLE_INTERIM INNER JOIN (rtblAUDIT_FREQ INNER JOIN rtblSCOPE
ON rtblAUDIT_FREQ.txtSCOPE_ID = rtblSCOPE.txtSCOPE_ID) ON
(qryASSIGN_CYCLE_INTERIM.txtSCOPE_ID = rtblAUDIT_FREQ.txtSCOPE_ID) AND
(qryASSIGN_CYCLE_INTERIM.RISK_LEVEL = rtblAUDIT_FREQ.txtRISK_LEVEL)
ORDER BY qryASSIGN_CYCLE_INTERIM.txtAENO;

Any ideas?

 

[[MVP] S.Clark]

Is NEXTAUD02 ever Null?

FWIW, Null + 1 = Null

 

[Dale Fye]

Do you have any NULL values in your [NextAud02] column?

Try wrapping that column in the NZ() function, something like:

IIf([YNOOVERRIDE]=-1,NZ([NEXTAUD02],0) + 1,NZ([NEXTAUD02], 0) AS NEXTAUD,

HTH
Dale

 

[Biggles]

That solved it, it wasn't a null, but I had put in a n/a. I have been sick
for a few days, so that might be it. But looking for nulls picked up the
error. Thanks to both of you.

Sean
--
Ficticiously Yours, Biggles

Do you have any NULL values in your [NextAud02] column?

Try wrapping that column in the NZ() function, something like:

IIf([YNOOVERRIDE]=-1,NZ([NEXTAUD02],0) + 1,NZ([NEXTAUD02], 0) AS NEXTAUD,

HTH
Dale

When I attempt to perform a group by (either select or crosstab query) on
a
field calculated in a previous query, I get a "Data Type mismatch in
criteria
expression" error. I am not sure why this occurs. I can display the
field
fine if I don't have the total line showing in the design view. This is
the
SQL I am trying to run, and various tests have limited it down to the
NEXTAUD
field as the problem.

TRANSFORM Count(qryASSIGN_CYCLE.txtAENO) AS CountOftxtAENO
SELECT qryASSIGN_CYCLE.txtAENO, qryASSIGN_CYCLE.NEXTAUD
FROM qryASSIGN_CYCLE
GROUP BY qryASSIGN_CYCLE.txtAENO, qryASSIGN_CYCLE.NEXTAUD
PIVOT qryASSIGN_CYCLE.txtSCOPE_ID;

The qryASSIGN_CYCLE looks like this (with the **** added to offset the
nextaud, this query works appropriately):

SELECT qryASSIGN_CYCLE_INTERIM.txtAENO,
qryASSIGN_CYCLE_INTERIM.RISK_LEVEL,
qryASSIGN_CYCLE_INTERIM.txtSCOPE_ID, qryASSIGN_CYCLE_INTERIM.LAST_AUDIT,
rtblAUDIT_FREQ.intCYCLE,
****
IIf([YNOOVERRIDE]=-1,[NEXTAUD02]+1,[NEXTAUD02]) AS NEXTAUD,
*****
[NEXTAUD]+[INTCYCLE] AS NEXTAUD1, [NEXTAUD1]+[INTCYCLE] AS NEXTAUD2,
[LAST_AUDIT]+[INTCYCLE] AS NEXTAUD01, rtblSCOPE.txtSCOPE,
IIf([nextaud01]<2006,2006,[nextaud01]) AS NEXTAUD02,
qryASSIGN_CYCLE_INTERIM.ynoOVERRIDE
FROM qryASSIGN_CYCLE_INTERIM INNER JOIN (rtblAUDIT_FREQ INNER JOIN
rtblSCOPE
ON rtblAUDIT_FREQ.txtSCOPE_ID = rtblSCOPE.txtSCOPE_ID) ON
(qryASSIGN_CYCLE_INTERIM.txtSCOPE_ID = rtblAUDIT_FREQ.txtSCOPE_ID) AND
(qryASSIGN_CYCLE_INTERIM.RISK_LEVEL = rtblAUDIT_FREQ.txtRISK_LEVEL)
ORDER BY qryASSIGN_CYCLE_INTERIM.txtAENO;

Any ideas?