Using iif for nulls is not working

[vba317]

I have a query that is giving me null values. So when I copy the results into a spreadsheet I get blanks. I need to rework the query to avoid doing this. If have tried using the iif statement. I don't get an error but I am still getting blanks. I was hoping to get some assistance. The code is in the field of the query.

Tom

Total Of workrvu: IIf(IsNull([workrvu]),0,([workrvu]))

 

[TheFitz]

I haven't tried this, but I'm sure I've seen something similar. You've got 2 choices:

a) Try IIf([workrvu]=Null,0,[workrvu])
b) Write a short function in VBA to check the value.

I think a) wil work as I'm sure I've come across something very similar, yet I can't remember over what.

Give it a try and let me know if it works,

Thanks

F.

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!

 

[dhookom]

I would try:

Total Of workrvu: Nz([workrvu],0)

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

Dhookom,
I tried your solution but I am sill getting blanks.

 

[TheFitz]

How sure are you that it's returning NULL values and not empty strings?

Try:

a) IIf(Len([workrvu])<0,0,([workrvu]))

- or -

b) IIf([workrvu]="",0,([workrvu]))

F.

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!

 

[vba317]

Fitz,

I tried the solution IIf([workrvu]=Null,0,[workrvu])and IIf(Len([workrvu])<0,0,([workrvu]))
I am getting the error You tried to execute a query that does not include the specified expression IIf(Len([workrvu])<0,0,([workrvu])) as part of an aggregate function

 

[genomon]

Dhookom,
I tried your solution but I am sill getting blanks.

I agree with Duane this is a good solution. I am guessing if you are getting blanks it is because that is what is stored in [workrvu] which could happen if it is not defined as numeric, or if you are sending the data to Excel using VBA a parameter may be missing, or maybe the spreadsheet itself is formtting its cells to print blanks for zeroes?
Just some random thoughts...

Beir bua agus beannacht!

 

[dhookom]

What are you seeing when you view the query in Access?


Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Did you try:
[tt]
Total Of workrvu: IIf(Trim([workrvu] & "") = "", 0, [workrvu])
[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[vba317]

I didn't think this mattered but maybe it does. The query I am having the problem with is part of a cross-tab query. So if I look at the table the query is based on, the table has no blanks or zeros. But this is a query that is based on seeing a code for every month in the year. So even thou the table has no zeros in it. As the data gets aggregated there could be data in Jan for a code and no info in Mar and this would be acceptable.

TRANSFORM IIf(Trim([workrvu] & "")="",0,[workrvu]) AS [Total Of workrvu]
SELECT [cptcode] & "-" & [cptcomp] AS CPT, dbo_tmp_rpt_dat_CPTRVU.cptdesc, dbo_tmp_rpt_dat_CPTRVU.unitrvu
FROM dbo_tmp_rpt_dat_CPTRVU
WHERE (((dbo_tmp_rpt_dat_CPTRVU.unitrvu)>0))
GROUP BY [cptcode] & "-" & [cptcomp], dbo_tmp_rpt_dat_CPTRVU.cptdesc, dbo_tmp_rpt_dat_CPTRVU.unitrvu
ORDER BY [cptcode] & "-" & [cptcomp]
PIVOT dbo_tmp_rpt_dat_CPTRVU.rptpd;

 

[SkipVought]

Why do you "copy the results into a spreadsheet" rather than perform the query in a QueryTable in your sheet, or via ADO? The empty cells would be where the belong.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[vba317]

The problem is I am copying the results of the query into a spreadsheet which feeds another sheet. When the blank value gets into the final spreadsheet from the data worksheet I get a #VALUE! error. I want to get rid of the #VALUE! error, the only way to do that is to have a zero in every cell in the data sheet. I was hoping that there would be an automated solution vs me entering 1000's of zeros into this worksheet.

 

[genomon]

Being a database developer, I am not so fluent with Excel but I seem to recall either a formula or FormatCell solution for this. I will ask around the shop and post back tomorrow if I find anything.

Beir bua agus beannacht!

 

[vba317]

Sometimes I get lucky and an idea comes to me.

In the final sheet I added the following code and it works fine.

=IF(ISBLANK(DATA!D2),0,(DATA!D2))

 

[genomon]

Beir bua agus beannacht!

 

[combo]

Nz in cross-tab query works, for instance:
TRANSFORM Nz(sum([workrvu]),0) AS [Total Of workrvu] ...

combo