Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Aug 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need to show zero values in a query (Access 97)

    Hello, hope somebody here can help me... I have a query that list defects recorded in a user defined date range. That query is then used as the source for a Cross Tab query that cross-tabs count of defect type by calendar month. Defect types are stored in one table, defect transactions in another along with date etc. When I cross-tab the results, defect types that have no defects recorded against them appear as a blank (null) value. That is, the zero value is suppressed. Unfortunately, in the management reports that are produced, they (QA Managers) want to see a Zero value, not a null value. (they postulate that a null could be interpreted as "we forgot to count those values...", NOT "we had no defects of that type..)

    Is there a simple way to "un"-suppress zero values. I had thought of doing an Immediate If, (that is, Iif count=Null, show "0", otherwise show Count) but cannot get that to work...
    I also thought of giving each transaction a notional value of "1", then summing the results in a cross tab, but since a defect count is null because we haven't recorded any transactions of that type, you can't sum something that isn't there! A third option was to use the immediate if in the "OnFormat" event of the report itself - still can't make it work...

    Any help would be hugely appreciated. Thanks in advance.

    THE SQL statement for the current query is as follows:

    TRANSFORM Count(QryComplaintSummary.RecordID) AS [The Value]
    SELECT QryComplaintSummary.DefectTypeDesc, Count(QryComplaintSummary.RecordID) AS [Total Of RecordID]
    FROM QryComplaintSummary
    GROUP BY QryComplaintSummary.DefectTypeDesc
    PIVOT Format([Complaint Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need to show zero values in a query (Access 97)

    Replace

    Count(QryComplaintSummary.RecordID)

    by

    Nz(Count(QryComplaintSummary.RecordID),0)

    The Nz function returns its first argument but replacess null values by the second argument. Usually, the result of Nz is text. If you need numeric values for further calculations, use

    CLng(Nz(Count(QryComplaintSummary.RecordID),0))

  3. #3
    New Lounger
    Join Date
    Aug 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need to show zero values in a query (Access 97)

    That worked a treat! I had to use teh Clng version as I'm totalling columns also, but otherwise, an easy & elegant fix. I've never used that function before, so Its good to learn something new.

    Thanyou Hans. It must be hard to be humble when you're that good! :-)

    dp

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need to show zero values in a query (Access 97)

    Being active in the Lounge makes me aware of how much I don't know. I learn something new here every day...

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Need to show zero values in a query (Access 97)

    Mac Davis, wasn't it?

Posting Permissions

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