Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    No data report (WIN 2000 Acc 97)

    I have asked this before, but either did not understand the answer (more than likely) or the suggestion didn't work (least likely)
    or it just was overlooked.
    I have attached a mini sample in case I don't articulate the issue clearly.

    When you run the report (recordsource is a simple query, which is based on a simple table), you are prompted for
    a Begin Date and an End Date. (Works fine). Type in 4/1/2003 as Begin Date, 4/29/2003 as End Date, everything
    is wonderful.
    Then, run the report again, and type in 4/1/2000 Begin Date, 4/29/2000 End Date.
    When the report opens, it is blank where the 2 dates would go, the Request Status and Count are also blank, and there is an error# where the total would go.
    Now I understand this is the correct behavior of Access. I have been able to have a message box pop up (On NoData) letting the user know there are no results. BUT:
    The boss wants a report to open (even with no data) and still have everything filled in (Dates - and zeros)
    I tried using RecordCount: Count(NZ([Test_ID],0)) in the query, but it didn't produce a zero.

    Thank you for your help,
    Michael
    Attached Files Attached Files

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No data report (WIN 2000 Acc 97)

    Michael,

    Try something like this as your query:
    <font face="Georgia">
    SELECT DISTINCTROW TEST_TABLE.STATUS_TYPE, Sum(IIf([DATE_IN]>=[Begin Date] And [DATE_IN]<=[End Date],1,0)) AS InRange
    FROM TEST_TABLE
    GROUP BY TEST_TABLE.STATUS_TYPE
    ORDER BY TEST_TABLE.STATUS_TYPE;
    </font face=georgia>
    Then display 'InRange' in your report rather than 'CountOfTESTID'. This technique does have the disadvantage that it also shows zero counts when other status types are non-zero (you always get a total for every status type whether they're non-zero or zero). But maybe that's not so bad...

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No data report (WIN 2000 Acc 97)

    I wish my smile looked as good as -----> <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Thank you so much Tom. It is EXACTLY what I need. I love the fact that we get a total
    for every status type whether they're non-zero or zero. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    Much, much appreciated Tom.

    Michael

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

    Re: No data report (WIN 2000 Acc 97)

    Hello Michael,

    There is a fundamental principle at work here. If you have a query that returns records, some of the fields may be empty. You can use the Nz function to replace these by 0, or whatever you like. But if your query returns no records, there are no data at all. There is nothing that can be replaced by a 0.

    It is possible to get around this by using a form to enter the query parameters.
    <UL><LI>Create a form frmDates.
    <LI>Place to text boxes txtBeginDate and txtEndDate on the form; set their Format property to one of the date formats.
    <LI>Replace the where-condition of the query by Between [Forms]![frmDates]![txtBeginDate] And [Forms]![frmDates]![txtEndDate]
    <LI>With the query in design view, select Query | Parameters... and enter [Forms]![frmDates]![txtBeginDate] as parameter, of type Date/Time
    <LI>Also enter [Forms]![frmDates]![txtEndDate] as parameter, of type Date/Time
    <LI>Then click OK and save the query.
    <LI>Set the control source of the text boxes in the report header to =[Forms]![frmDates]![txtBeginDate] and =[Forms]![frmDates]![txtEndDate], respectively.
    <LI>Put a label lblNoData with caption "None" (or "0", or whatever you like) on top of the text box with the count in the report footer.
    <LI>Set its background style to Normal, and its Visible property to No.
    <LI>Put the following line of code in the On No Data event procedure of the report:
    lblNoData.Visible = True
    <LI>Put a command button on the frmDates form that opens the report with
    DoCmd.OpenReport "TEST REPORT", acViewPreview
    (substitute the name of your report)[/list]See attached version of your sample.
    Attached Files Attached Files

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No data report (WIN 2000 Acc 97)

    <img src=/S/thewave.gif border=0 alt=thewave width=225 height=33>

    Hans: I feel like I hit the Jackpot today !! It so happens I have different reports and can actually
    use BOTH Tom's suggestion on some, and yours on a couple of others - just amazing - my cube
    neighbor wanted to know why I was shaking my head so much today !!

    If you guys are ever in Sacramento,CA let me know - <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>drinks and <img src=/S/chef.gif border=0 alt=chef width=19 height=22> dinner are on me!!

    Michael

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: No data report (WIN 2000 Acc 97)

    Good grief, another Sacramentan! We should start a 12-step group for Sacramento Lounge addicts! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Charlotte

  7. #7
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No data report (WIN 2000 Acc 97)

    Careful what you offer, Michael!!

    We're going to be in/near Sacramento the last week of June for the UC Davis Volleyball Festival!!! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  8. #8
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No data report (WIN 2000 Acc 97)

    <img src=/S/hushmouth.gif border=0 alt=hushmouth width=16 height=16>

Posting Permissions

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