Results 1 to 2 of 2
  1. #1

    Displaying Zeros in Reports (Access 2000)

    Greetings again...
    I'm sure you've been asked this several times, as it seems like such a mundane question... but I'm trying to tally up yes/no boxes for a report. I may have gone about it all wrong so let me first explain what I've done... I've attached a sample database - so I'll just explain what I did to get where I am w/ it.
    First I created a table - YesNoTable - with 2 checkbox (yes/no) fields - I then put in two records (1 yes and 1 no)
    Next I created a query titled YesQuery (using the query wizard) which would query the 'Yes' column of my table - I clicked on the summary options button and selected 'count records in YesNoTable' once finished with the wizard I modified the design of the query w/ a =True statement in the criteria (so it would only return boxes with the check)
    I followed the same process creating a NoQuery query which would query the 'No' column of the table...
    To tie them together for my report I created another query which would pool the "Count Of YesNoTable: Count(*)" column of both queries and return two simple colums for a recordsource for my report...
    However (you saw this coming didn't you?) It will only return numbers if there is at least one instance of Yes and No being checked... So I find myself in this conundrum and I turn to you for a way out...
    Any thoughts/suggestions/solutions will be appreciated [img]/forums/images/smilies/tongue.gif[/img]
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Displaying Zeros in Reports (Access 2000)

    You can count the number of Yes and No's in one query. Here is the SQL of the query:

    SELECT Abs(Sum([Yes])) AS CountOfYes, Abs(Sum([No])) AS CountOfNo
    FROM YesNoTable

    You create this query by creating a new query based on YesNoTable in design view. The definition of the two fields is CountOfYes: Abs(Sum([Yes])) and CountOfNo: Abs(Sum([No])).

Posting Permissions

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