Data type mismatch in criteria expression ? 2

[1DMF]

Hi ,

Why is my CrossTab Query giving me a data type mismatch error?

here is the SQL

TRANSFORM Sum(Format([Cnt],"Fixed")) AS Expr1
SELECT rptOfficers.Ord, rptOfficers.Cat, Sum(rptOfficers.Cnt) AS Total
FROM rptOfficers RIGHT JOIN rptAlias ON rptOfficers.Alias = rptAlias.Alias
GROUP BY rptOfficers.Ord, rptOfficers.Cat
ORDER BY rptOfficers.Ord
PIVOT rptAlias.Alias;

Thanks,
1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

format function returns a string and a sum of a string could cause this.
Sum(fix([cnt]))
or int([cnt])

 

[PHV]

Replace this:
TRANSFORM Sum(Format([Cnt],"Fixed")) AS Expr1
with this:
TRANSFORM Format(Sum([Cnt],"Fixed")) AS Expr1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

Thanks guys,

Funny how it worked on one CTQ and not another, I guess as you say, one is returning a string and the other isn't.

But looking closer I think it is because it is getting a NULL.

Anyways, works fine now, so again thanks.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts