Crosstab result returns blank cells, how do I change to show zero's?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a crosstab query comparing sales month on month. In the data I have a date column and a sales value column. When there is client revenue for April but not for May, the cell in the May column returns a blank. When I then make a differences column in excel it gives me a #value! response. How do I get the crosstab to return a zero instead?
 
With this type of question, you should post your SQL view. You can change
the expression for the Value column to
TheValue: Val(Sum(Nz([YourField],0)))
Total: Expression

--
Duane Hookom
MS Access MVP


Russell said:
I have a crosstab query comparing sales month on month. In the data I have
Click to expand...
a date column and a sales value column. When there is client revenue for
April but not for May, the cell in the May column returns a blank. When I
then make a differences column in excel it gives me a #value! response. How
do I get the crosstab to return a zero instead?
 
A couple ideas
1) easy way, not as flexible: you could base a second query on the result of the crosstab and format all the columns so that the null shows ("0;0;0;0", or whatever your using). If the column names change (like you have "May/04") then this won't work
2) What I've done for this is to first pregenerate column & row headings, then left join that into the data on the month and totalize on Nz([yourcalcfield],0). The Crosstab uses my pregenerated column headings
An easy way to generate the months is use a table with a single column of ascending integers (comes in very handy!) say tInteger, then base a query on it like

SELECT DateAdd("m",-,bom(Date())) AS dtmMonthStar
FROM tIntege
WHERE (((tInteger.I) Between 0 And 12))

Generate a unique list of your specific row headings, then drop in your months query above, and *don't* join them and so you get the cross product of all months/headings. Left join this to your data and away you go. Warning, this can run slowly sometimes - you may have to optimize it. Best Regards - Peter.
 
Actually, follow Duane's its a lot easier. I learned something today
Only the formula should be Val(nz(Sum([your field]),0)) or you'll get a data conversion error. Pete
 
Back
Top