Results 1 to 2 of 2
2003-01-30, 13:16 #1
- Join Date
- Jun 2001
- Niagara Falls, New York, USA
- Thanked 0 Times in 0 Posts
Cross Tab Report Not Showing Zeros? (A2k (9.0.3821) SR-1)
I have the following crosstab query that works OK, however is returning zero and space filled amounts on some of the line items that have zero values.
lngEstCost in number long integer
The crosstab query is driven by a query on a table, I checked lngEscCost in the query and table and all records contain a zero or valid number.
Partial Report output:
Permit Type, # and Description Total Cost Jan Feb Mar
0510 25 Plumbing Commerical _______0 __0 __0 ___
0510 30 Plumbing Residential ________0 ___ ___ __0
I tried wrapping lngEstCost with Nz with no success.
The first item has a valid total cost of 0, Jan is 0, Feb is 0, Mar is space, why isn
2003-01-30, 14:42 #2
- Join Date
- Dec 2000
- Sacramento, California, USA
- Thanked 1 Time in 1 Post
Re: Cross Tab Report Not Showing Zeros? (A2k (9.0.3821) SR-1)
You will get that result when you don't have a value in the underlying table for a period. Using the column headings means you will always get a column for Mar (or whatever) even if there is no data for that month. A crosstab can't insert a missing value, it can only return values that actually exist. You can't use Nz to force a zero into the crosstab, but you can use it at a higher level in a report based on the crosstab.Charlotte