Mismatch Error in a Crosstab Query

[ddrumm]

Hi folks

I have the below into a crosstab (which is working fine)
K3: IIf(([UOM_Code]='K3'),IIf([RegisterTypeCode]='IS' Or 'WT',[Non-Loss],""))

then ive ammended the same Query to look like this, so i will just see ES figures

ESonly: IIf(([UOM_Code]='KWH'),IIf([RegisterTypeCode]='ES',[Non-Loss],""))

This runs fine in the query, but when i try and put it in the same crosstab im getting a Mismatch error

Would anyone know what the problem is here?
as the first one is working fine i cant understand why the second one won't run

 

[namliam]

what is the ful crosstab and full query?

P.s. Welcome to the forum

 

[ddrumm]

Hi Namliam
and Thank you

Heres the Crosstab Query (Its a big one)

TRANSFORM Sum(qryevery30mins.[Non-Loss]) AS [SumOfNon-Loss]
SELECT tblMPRN.CustName, qryevery30mins.MPRN, tblMPRN.MICKVA, tblMPRN.Contyp, tblMPRN.Duos, tblMPRN.Tuos, Sum(IIf(Weekday([Dte2]) Not In (1,7),qryevery30mins.[Between Hrs K3],0)) AS [Total K3], Sum(IIf(Weekday([Dte2]) Not In (1,7),qryevery30mins.[Between Hrs KWH],0)) AS [Total KWH], Sum(qryevery30mins.HHIntervalValue) AS SumOfHHIntervalValue, Sum(qryevery30mins.[K3+KWH]) AS [SumOfK3+KWH], Sum(qryevery30mins.K3s) AS SumOfK3s, Sum(qryevery30mins.KWHs) AS SumOfKWHs, Sum(qryevery30mins.[IS&WT]) AS [SumOfIS & WT], Sum(qryevery30mins.ESOnly) AS SumOfESOnly
FROM qryevery30mins INNER JOIN tblMPRN ON qryevery30mins.MPRN = tblMPRN.MPRN
WHERE (((qryevery30mins.Dte2) Between #1/1/2009# And #1/31/2009#))
GROUP BY tblMPRN.CustName, qryevery30mins.MPRN, tblMPRN.MICKVA, tblMPRN.Contyp, tblMPRN.Duos, tblMPRN.Tuos, qryevery30mins.UOM_Code
PIVOT qryevery30mins.Dte2;




And this is the Query

SELECT dbo_METERINTERVAL_ROI.METERINTERVAL_ROI_Key, dbo_METERINTERVAL_ROI.MPRN, dbo_METERINTERVAL_ROI.MeterCategoryCode, dbo_METERINTERVAL_ROI.ChannelStatus, dbo_METERINTERVAL_ROI.HHIntervalStatusCode, dbo_METERINTERVAL_ROI.MeteringInterval, dbo_METERINTERVAL_ROI.RegisterTypeCode, dbo_METERINTERVAL_ROI.IntervalPeriodTimestamp, dbo_METERINTERVAL_ROI.UOM_Code, dbo_METERINTERVAL_ROI.HHIntervalStatusCode, dbo_METERINTERVAL_ROI.HHIntervalValue, dbo_METERINTERVAL_ROI.HHIntervalValue, DateValue([IntervalPeriodTimestamp]) AS Dte2, TimeValue([IntervalPeriodTimestamp]) AS Tme2, IIf([RegisterTypeCode]="ES",-[HHIntervalValue],[HHIntervalValue]) AS KHKwh, [HHIntervalValue]+[KHKwh] AS Kwh30mins, tblMPRN.CustName, tblMPRN.LossAdj1, tblMPRN.LossAdj2, [HHIntervalValue]*[LossAdj1] AS totloss, [dbo_METERINTERVAL_ROI.MPRN] & [dbo_METERINTERVAL_ROI.RegisterType] AS UniqueCust, tblMPRN.HighestImpCde, IIf(Hour([Tme2])>=0 And Hour([Tme2])<=24,[LossAdj1]) AS incloss, dbo_METERINTERVAL_ROI.UOM_Code AS K3, dbo_METERINTERVAL_ROI.UOM_Code AS KWH, IIf([UOM_Code]='K3',(IIf(Hour([Tme2])>=16 And Hour([Tme2])<=18,[Non-Loss]))) AS [Between Hrs K3], IIf([K3s]>=[KWHs]/2,[K3s]-([KWHs]/2),0) AS [KVarhs In Excess], [KHKwh] AS [Non-Loss], IIf([UOM_Code]='KWH',(IIf(Hour([Tme2])>=16 And Hour([Tme2])<=18,[Non-Loss]))) AS [Between Hrs KWH], IIf([UOM_Code]='K3' Or 'KWH',(IIf(Hour([Tme2])>=16 And Hour([Tme2])<=18,[Non-Loss]))) AS [K3+KWH], IIf([UOM_Code]='K3',[Non-Loss]) AS K3s, IIf([UOM_Code]='KWH',[Non-Loss]) AS KWHs, dbo_METERINTERVAL_ROI.RegisterTypeCode, IIf([IS or ES] In ('True'),nz([Non-Loss])+nz([Non-Loss])) AS Test, IIf([RegisterTypeCode]="WT" Or [RegisterTypeCode]="IS","True","False") AS [IS or ES], IIf(([UOM_Code]='KWH'),IIf([RegisterTypeCode]='ES',[HHIntervalValue],"")) AS ESOnly, IIf(([UOM_Code]='K3'),IIf([RegisterTypeCode]='IS' Or 'WT',[Non-Loss],"")) AS [IS&WT]
FROM dbo_METERINTERVAL_ROI LEFT JOIN tblMPRN ON (dbo_METERINTERVAL_ROI.RegisterType = tblMPRN.RegisterType) AND (dbo_METERINTERVAL_ROI.MPRN = tblMPRN.MPRN)
WHERE (((dbo_METERINTERVAL_ROI.HHIntervalStatusCode)="VVAK"));

Hope this makes sence
Thanks

 

[namliam]

Thanks for splashing your sql without properly formatting it!!!!

SELECT dbo_METERINTERVAL_ROI.METERINTERVAL_ROI_Key
     , dbo_METERINTERVAL_ROI.MPRN
     , dbo_METERINTERVAL_ROI.MeterCategoryCode
     , dbo_METERINTERVAL_ROI.ChannelStatus
     , dbo_METERINTERVAL_ROI.HHIntervalStatusCode
     , dbo_METERINTERVAL_ROI.MeteringInterval
     , dbo_METERINTERVAL_ROI.RegisterTypeCode
     , dbo_METERINTERVAL_ROI.IntervalPeriodTimestamp
     , dbo_METERINTERVAL_ROI.UOM_Code
     , dbo_METERINTERVAL_ROI.HHIntervalStatusCode
[B]     , dbo_METERINTERVAL_ROI.HHIntervalValue
     , dbo_METERINTERVAL_ROI.HHIntervalValue[/B]
     , DateValue([IntervalPeriodTimestamp])               AS Dte2
     , TimeValue([IntervalPeriodTimestamp])               AS Tme2
     , IIf([RegisterTypeCode]="ES",-[HHIntervalValue],[HHIntervalValue])     AS KHKwh
     , [HHIntervalValue]+[KHKwh]                          AS Kwh30mins
     , tblMPRN.CustName
     , tblMPRN.LossAdj1
     , tblMPRN.LossAdj2
     , [HHIntervalValue]*[LossAdj1]                       AS totloss
[B]     , [dbo_METERINTERVAL_ROI.MPRN] & [dbo_METERINTERVAL_ROI.RegisterType]          AS UniqueCust[/B]
     , tblMPRN.HighestImpCde
     ,[B] IIf(Hour([Tme2])>=0 And Hour([Tme2])<=24,[LossAdj1]) [/B]          AS incloss
     , dbo_METERINTERVAL_ROI.UOM_Code                     AS K3
     , dbo_METERINTERVAL_ROI.UOM_Code                     AS KWH
     , IIf([UOM_Code]='K3',(IIf(Hour([Tme2])>=16 And Hour([Tme2])<=18,[Non-Loss])))    AS [Between Hrs K3]
     , IIf([K3s]>=[KWHs]/2,[K3s]-([KWHs]/2),0)            AS [KVarhs In Excess]
     , [KHKwh]                                            AS [Non-Loss]
     , IIf([UOM_Code]='KWH',        (IIf(Hour([Tme2])>=16 And Hour([Tme2])<=18,[Non-Loss]))) AS [Between Hrs KWH]
     , IIf([UOM_Code]='K3' Or 'KWH',(IIf(Hour([Tme2])>=16 And Hour([Tme2])<=18,[Non-Loss]))) AS [K3+KWH]
     , IIf([UOM_Code]='K3' ,[Non-Loss])                   AS K3s
     , IIf([UOM_Code]='KWH',[Non-Loss])                   AS KWHs
     , dbo_METERINTERVAL_ROI.RegisterTypeCode
     , IIf([IS or ES] In ('True'),nz([Non-Loss])+nz([Non-Loss]))                             AS Test
     , IIf([RegisterTypeCode]="WT" Or [RegisterTypeCode]="IS","True","False")                AS [IS or ES]
     , IIf(([UOM_Code]='KWH'),IIf([RegisterTypeCode]='ES',[HHIntervalValue],""))             AS ESOnly
     , IIf(([UOM_Code]='K3') ,IIf([b][RegisterTypeCode]='IS' Or 'WT'[/b],[Non-Loss],""))     AS [IS&WT]
FROM dbo_METERINTERVAL_ROI 
LEFT JOIN tblMPRN ON (dbo_METERINTERVAL_ROI.RegisterType = tblMPRN.RegisterType) 
                 AND (dbo_METERINTERVAL_ROI.MPRN         = tblMPRN.MPRN)
WHERE (((dbo_METERINTERVAL_ROI.HHIntervalStatusCode)="VV AK"));

TRANSFORM Sum(qryevery30mins.[Non-Loss]) AS [SumOfNon-Loss]
SELECT tblMPRN.CustName
     , qryevery30mins.MPRN
     , tblMPRN.MICKVA
     , tblMPRN.Contyp
     , tblMPRN.Duos
     , tblMPRN.Tuos
     , Sum(IIf(Weekday([Dte2]) Not In (1,7),qryevery30mins.[Between Hrs K3] ,0)) AS [Total K3]
     , Sum(IIf(Weekday([Dte2]) Not In (1,7),qryevery30mins.[Between Hrs KWH],0)) AS [Total KWH]
     , Sum(qryevery30mins.HHIntervalValue)  AS SumOfHHIntervalValue
     , Sum(qryevery30mins.[K3+KWH])         AS [SumOfK3+KWH]
     , Sum(qryevery30mins.K3s)              AS SumOfK3s
     , Sum(qryevery30mins.KWHs)             AS SumOfKWHs
     , Sum(qryevery30mins.[IS&WT])          AS [SumOfIS & WT]
     , Sum(qryevery30mins.ESOnly)           AS SumOfESOnly
FROM       qryevery30mins 
INNER JOIN tblMPRN        ON qryevery30mins.MPRN = tblMPRN.MPRN
WHERE (((qryevery30mins.Dte2) Between #1/1/2009# And #1/31/2009#))
GROUP BY tblMPRN.CustName, qryevery30mins.MPRN, tblMPRN.MICKVA, tblMPRN.Contyp, tblMPRN.Duos, tblMPRN.Tuos, qryevery30mins.UOM_Code
PIVOT qryevery30mins.Dte2;

[dbo_METERINTERVAL_ROI.MPRN] should just be dbo_METERINTERVAL_ROI.MPRN, I think... though it may not matter

IIf(Hour([Tme2])>=0 And Hour([Tme2])<=24,[LossAdj1]) This doesnt make sence?? This is always true??


[RegisterTypeCode]='IS' Or 'WT'
This doesnt work... either use in or the proper or syntax... This could be your (current) problem
[RegisterTypeCode]='IS' Or [RegisterTypeCode]='WT'
[RegisterTypeCode] in ('IS', 'WT' )

Not all of your IIF's have an "Else" part, this could cause problem or unexpected values.


I hope this gets you someplace.

Good luck!

 

[ddrumm]

Oops im sorry for not formatting it,,,,ive alot to learn on this new site.

ill check those errors out and ill let you know how i get on

Thanks again

 

[namliam]

Oops im sorry for not formatting it,,,,ive alot to learn on this new site.

Nothing particular to the site or forum.
1) Nice to do for someone to be looking at your Code, that it is actually somewhat readable
2) Makes it more managable for yourself (you may have spotted some of above yourself if you had formatted)

ill check those errors out and ill let you know how i get on

Good luck !

 

[ddrumm]

Hi again
Ive tried the aboue and even made the query smaller jus so I can see those ES figures and im still getting the same error

 

[namliam]

The only thing I hadnt mentioned yet... is this
IIf([RegisterTypeCode]='IS' Or 'WT',[Non-Loss],""))

Non-Loss seems to be a number field, "" obviously is a string. Perhaps you need to repalce this by NULL instead or 0??

Other than that I am at a loss.

 

[ddrumm]

yes Non_Loss is a number field, I need to display the Non-Loss figures if the statement is true

What I cant understand is this one works
ISWT: IIf(([UOM_Code]='K3'),IIf([RegisterTypeCode]='IS' Or [RegisterTypeCode]='WT',[HHIntervalValue],""))

but the below doesnt
ESonly: IIf(([UOM_Code]='KWH'),IIf([RegisterTypeCode]='ES',[Non-Loss],""))

there both the same only i need to view the ES figures seperate in this second one

 

[namliam]

again the only thing I can think of is the number field thing... I dont know why one works and the other doesnt...

I know access can do some strange things with excel files when you import them, looking at the first few rows to find out if something is text or number. If the first few rows are blank, it will assume number. Then finding strings below will generate the same error... Perhaps something simular is going on here.
Try replacing "" by 0 and see if that helps?

It is IMHO interperting that column as a string as a result of the "", then doing Sum() on a text field will generate this error.

 

[ddrumm]

Ive tried this also and still nothing,,,,
Ahhhhhh i dont know what im going to do now

but Thanks for your help and patience all the same
greatly appreciated