nulls in crosstab (1 Viewer)

[GinaO]

Somebody please help me !!!


I finally managed to get my data together correctly in my final query which is a crosstab.

Now i want to perform a subtraction on the columns of the crosstab and because of the blanks/nulls my results are incorrect.

eg. if i have 2 column headings in my crosstab , i want to generate a third column which is column 1 minus column 2.


i tried to use nz, isempty,ismissing to convert the blanks to zero so that i can subtract but nothing works .


If sum1 knows how to deal with this please please please help me ..

Thanks,
Gina.

 

[GinaO]

I've been at this for days and still can't get through.

If my post wasn't clear i'm be glad to elaborate.

But somebody PLEASE help !!!

 

[Jon K]

Nz([Column 1]) - Nz([Column 2])

 

[NOL]

Thanks but that doesn't work ..

gives #error on the report
and i can't do the subtraction in the crosstab query either.

is there any other way out ?

 

[Sohaila Taravati]

Make another query out of your crosstab and do the nz function there and see if that helps

 

[NOL]

Thanks Sohaila,

But i can't base a new query on a crosstab

NOL

 

[Sohaila Taravati]

Why not

 

[Alexandre]

If what JonK suggested did not work, maybe it is because you don' t only have nulls but aslo zero-length strings.
Give a try to:
Iif(len(nz([Column1],""))=0,0,[Column1]) - Iif(len(nz([Column2],""))=0,0,[Column2])