Nz Function--SQL View

[Bass71]

Ok, here it is. I've tried everyway to Sunday... I have no problem with the IsNull or Is Not Null functionality, but the Null to Zero's killin' me.

The following is one of my cracks at it:


SELECT CoreData.TAS, CoreData.[Date Opened], CoreData.[Transaction Volume]
FROM CoreData
GROUP BY CoreData.TAS, CoreData.[Date Opened]
HAVING (((CoreData.[Transaction Volume])=IIf(Nz("Transaction Volume&quot,"0&quot));


....Much appreciated ... Richard

 

[tlbroadbent]

Richard,

First I feel a need to make the following comment. Keep all postings for the same problem in one thread. We now have to refer to three threads to follow this problem. It is much easier in one thread.

The first posting referred to a crosstab query. This is not a crosstab query. Did you perhaps mean a Totals query? That is what this appears to be - almost!

The follwing query will return a total volume by TAS and Date Opened. Only totals > 0 will be returned.

SELECT
CoreData.TAS,
CoreData.[Date Opened],
Sum(Nz(CoreData.[Transaction Volume],0)) As TotVolume
FROM CoreData
GROUP BY CoreData.TAS, CoreData.[Date Opened]
HAVING Sum(Nz(CoreData.[Transaction Volume],0))>0;

Or perhaps you wanted to choose all the records with non NULL Volume values.

SELECT
CoreData.TAS,
CoreData.[Date Opened],
CoreData.[Transaction Volume]
FROM CoreData
WHERE CoreData.[Transaction Volume] IS NOT NULL;

Or another possibility is to simply return all records, while converting NULL Volumes to ZERO.

SELECT
CoreData.TAS,
CoreData.[Date Opened],
Nz(CoreData.[Transaction Volume],0) As TranVolume
FROM CoreData

I hope one of these helps. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Bass71]

Terry:

Thanks for the info. It worked beautifully in the Select query! Is it also possible to use the Nz functio in the 'Value' field of a Crosstab?

TRANSFORM Sum(Query1.[SumOfSumOfPrincipal Cash]) AS [The Value]
SELECT Query1.[Account #], Query1.[Relationship Name], Query1.Status
FROM Query1
GROUP BY Query1.[Account #], Query1.[Relationship Name], Query1.Status
PIVOT Query1.Month;

Richard