Results 1 to 2 of 2
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    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

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •