Crosstab Data Type Mismatch

[pickslides]

Hi all,

I am trying to construct a crosstab that averages a calculated field from a previous query. It is returning a "Data Type Mismatch" message.

The field I am trying to average is a subtraction of dates to find total days. I assume my field is not a number so I have tried to wrap it in CDbl() to change the type.

The formula is

CASE_DAYS: CDbl(IIf([Actual Close Date]-[Creation Date]>=0,[Actual Close Date]-[Creation Date],""))

Any tips?

MQ

 

[CJ_London]

probably because you are calculating a numeric value otherwise using a string - and you can't average strings

change the "" to 0

 

[pickslides]

Thanks for your help.

What you say does make sense. Although I don't want to assign a number because I am taking an average in the crosstab and putting in 0 will stew my results.

 

[CJ_London]

in that case try null

... Date]-[Creation Date],null))

see this link http://office.microsoft.com/en-gb/access-help/HV080761069.aspx

 

[pickslides]

Thanks again,

I want to round the avg figure in my crosstab. The SQL code is below, but the output is not rounded.

TRANSFORM Avg(SAP_CALD.[CASE_DAYS]) AS AvgOfCASE_DAYS
SELECT SAP_CALD.Model, Avg(Round([CASE_DAYS],2)) AS [Total Of CASE_DAYS]
FROM SAP_CALD
GROUP BY SAP_CALD.Model
PIVOT SAP_CALD.CLD_MONTH;

MQ

 

[JHB]

I would put the round function outside the AVG function, (not tested).

 Round(Avg([CASE_DAYS]),2)