Transform Troubles 1

[930driver]

I'm using a crosstab query behind a report. The query seems to work OK but I need to sum the columns. I'm running into trouble when a particular 'field' on the cross tab doesn't contain data. My group and report sums give me very strange results. I suspect it is because the missing missing field is actually null which goofs the sum. I have tried an IIF() in the Value column of the query to set it to zero if it's null but that doesn't work.

Any suggestions from anyone? "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[CosmoKramer]

Check out thread181-147077. I don't know if that will help or not. If not, could you post your code here??

 

[Databaseguy]

Yes, null will propegate. Have you tried the NZ function ??? Tyrone Lumley
augerinn@gte.net

http://server105.hypermart.net/databaseguy

 

[930driver]

Thanks, I'm not unfamiliar with that technique. I've already tried it and it doesn't work. Even though the values are indeed Null. I just verified that. So it's very strange to me that the IIF() doesn't seem to work. Since you asked, here's my sql code.

TRANSFORM Sum(IIf(IsNull([SumOfDistributionAmount]),0,[SumOfDistributionAmount])) AS Expr2
SELECT tblAccounts.AccountName, tblLedger.InvoiceNumber, tblCustomers.CustomerName
FROM ((tblCustomers RIGHT JOIN (tblLedger LEFT JOIN qrySumOfPaymentAmounts ON tblLedger.LedgerID = qrySumOfPaymentAmounts.LedgerID) ON tblCustomers.CustomerID = tblLedger.CustomerID) LEFT JOIN tblAccounts ON tblCustomers.AccountID = tblAccounts.AccountID) LEFT JOIN tblPayments ON qrySumOfPaymentAmounts.PaymentID = tblPayments.PaymentID
WHERE (((tblLedger.Deleted)=False) AND ((qrySumOfPaymentAmounts.SumOfDistributionAmount)>0))
GROUP BY tblAccounts.AccountName, tblLedger.InvoiceNumber, tblCustomers.CustomerName, tblLedger.DatePrinted
PIVOT Format([InvoiceDate],"mmmm&quot In ("January","February","March","April","May","June","July","August","September","October","November","December"
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[CosmoKramer]

How about this:

TRANSFORM IIf(IsNull(Sum([SumOfDistributionAmount])),0,Sum([SumOfDistributionAmount])) AS Expr2

 

[930driver]

I could have sworn I had tried that. I guess not because that did it. Thanks much. "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"