Field Returns No Data-Need to then Show as 0

[kaveman]

Hi Everybody,

I have a report that is driven by query similar to this:

SELECT DISTINCT (Select Sum([AMT]) FROM ACScust_sub3 WHERE TimeLeft >=365) AS AmtNotLastYear, ACScust_sub1.CAR
FROM ACScust_sub1, ACScust_sub3;

My problem is that certain select statements return no data. If they return no data I would want them to show up as 0 on my report but I can't get this to work.

Any suggestions?

 

[MStCyr]

Check out the NZ() function

 

[kaveman]

I have tried this. I get a run-time error saying,

'You can't assign a value to this object.'

 

[RV]

I have tried this. I get a run-time error saying,

'You can't assign a value to this object.'

How did you try?
Post your query including the Nz function.

RV

 

[kaveman]

Here is the entire query I was using the NZ function on the report but if the query is the better place to do this I'll take any pointers you have:

SELECT DISTINCT (Select Count(

) FROM ACScust_sub1 WHERE TimeLeft>=365) AS NotLastYear, (Select Count([Code]) FROM ACScust_sub1 WHERE TimeLeft >=0 and TimeLeft<365) AS LastYear, (Select Count([Code]) FROM ACScust_sub1 WHERE TimeLeft <0 and Renew like "1") AS EverGreen, (Select Count([Code]) FROM ACScust_sub1 WHERE TimeLeft is null or TimeLeft<0 and Renew like "0") AS NoContract, (Select Count([Code]) FROM ACScust_sub1 WHERE Tier like "Platinum") AS Platinum, (Select Count([Code]) FROM ACScust_sub1 WHERE Tier like "Gold") AS Gold, (Select Count([Code]) FROM ACScust_sub1 WHERE Tier like "Silver") AS Silver, (Select Count([Code]) FROM ACScust_sub1 WHERE Tier like "Bronze") AS Bronze, (Select Count([Code]) FROM ACScust_sub1 WHERE Tier like "Charity") AS Charity, (Select Count([Code]) FROM ACScust_sub1 WHERE Tier like "NA") AS NA, (Select Count([Code]) FROM ACScust_sub1 WHERE Tier like "Direct Sal") AS DirectSale, (Select Sum([AMT]) FROM ACScust_sub3 WHERE TimeLeft >=365) AS AmtNotLastYear, (Select Sum([AMT]) FROM ACScust_sub3 WHERE TimeLeft >=0 and TimeLeft<365) AS AmtLastYear, (Select Sum([AMT]) FROM ACScust_sub3 WHERE TimeLeft <0 and Renew like"1") AS AmtEvergreen, (Select Sum([AMT]) FROM ACScust_sub3 WHERE TimeLeft is null or TimeLeft<0 and Renew like "0") AS AmtNoContract, ACScust_sub1.CAR
FROM ACScust_sub1, ACScust_sub3;