How to show zeroes (newbie question)

[uludude1]

I have a report that runs off of a crosstab query. the column headings are "purchase" and "refinance". I want to have a total column next to that, if purchase or refi column equals zero, the report leaves it blank and
=[purchase]+[refinance] will not show anything. I can't figure out where to put the NZ() function in the query to produce a zero because it is crosstab. The column heading in the query is called PURCREFI. I hope that isn't to confusing. ANy suggestions? Thanks,
Dave

 

[WildHare]

You should be able to NZ(...) the two data columns individually, and get your totals column to sum up correctly.

Or, failing that, go back to your earlier query that is the basis for the crosstab, and zero them out at that step.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[uludude1]

when I NZ(...) in the control source i.e. NZ([purchase],0)
I get the #error. When I use the function in the query it has no effect. Here is an example

Purchase Refinance Total
3
1 5 6
3

So you can see what the problem is now. I am trying to get the total to produce a value at all times, not only when both fields show data. Thanks,
dave

 

[WildHare]

Is your cross tab going directly against the table? Would it be out of the question to go and run some update queries against the table to set the null guys to zero?

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[uludude1]

THis is the field from the table.

PURCREFI

pur
refi
refi
pur
pur
refi


so I am getting a count by company of how many purchase and how many refinances they had. So some have no purchases or no refinances, like in the table two posts up, but the zero isn't there. There are no null in the PURCREFI column in the original table. Thanks,
DAve