Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2005
    Posts
    6

    Unanswered: Crosstab to display zero's instead of null

    The SQL for my access db is as follows

    Code:
    TRANSFORM Count(statusreporthsc.SDC1) AS CountOfSDC1
    SELECT statusreporthsc.date2, Count(statusreporthsc.SDC1) AS [Total Of SDC1]
    FROM statusreporthsc
    GROUP BY statusreporthsc.date2
    PIVOT statusreporthsc.Category;
    How do I make it display all the zero's instead of blanks...if I can at all.

    Thanks
    Neil

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    nz(something,0)
    replaces any null something with 0

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2005
    Posts
    6

    Not sure I understand where to put it

    Could you help?

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    have a bash with:

    TRANSFORM nz(Count(statusreporthsc.SDC1),0) AS CountOfSDC1

    ...and see if it does the job.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2005
    Posts
    6

    Doh! Worked nicely in access but...

    I am pulling through the details of the DB to a web front end via ASP and it doesnt recognise the nz value. Any idea of a quick fix? If not, thanks for your time anyway.
    Neil

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    not - sorry, but ASP is not my thing.
    i checked my proposal on a local JET xtab and it seemed to work.

    ugly slow-fix that might fly...

    replace
    TRANSFORM Count(statusreporthsc.SDC1) AS CountOfSDC1
    with
    TRANSFORM iif(isnull(Count(statusreporthsc.SDC1)),0,Count(st atusreporthsc.SDC1)) AS CountOfSDC1

    (no certainty that IIF() works for ASP, but a brief google gives cause for optimism!)

    izy



    LATER: the ...(st<space>atusre.... is the site, not me.
    Last edited by izyrider; 12-29-05 at 14:12. Reason: bloody space @50
    currently using SS 2008R2

Posting Permissions

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