Results 1 to 10 of 10
  1. #1
    Join Date
    May 2014
    Posts
    35
    Provided Answers: 2

    Unanswered: count of yes/no field

    I have a query with the following SQL to count a yes/no check box.

    SELECT RAI.[Override not to Lodge], Count(RAI.[Override not to Lodge]) AS [CountOfOverride not to Lodge]
    FROM RAI
    GROUP BY RAI.[Override not to Lodge]
    HAVING (((RAI.[Override not to Lodge])=Yes));

    This works great. Does exactly what I want. My question is when I run a report if there are no yes responses I would like the report to show 0 and not leave it blank.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider putting some code in the report sections on format event, that tests if the value is null and if so either hide the control or cancle the format event
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2014
    Posts
    35
    Provided Answers: 2
    healdem, thanks for the response. Not having any luck with this. Any suggestions on the code and where to put it? Format event for the (header, detail)? newbe is stuck!

  4. #4
    Join Date
    May 2014
    Posts
    35
    Provided Answers: 2
    Still have not figured this out. Anyone have any ideas?

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    test the value of the control in the footer sections on format event

    if isnull(mycontrol.text) then
    mycontrol.text = 0
    endif
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    May 2014
    Posts
    35
    Provided Answers: 2
    healdem,

    Thank you so much for trying to help. I can't get this to work.

    if isnull(mycontrol.text) then
    mycontrol.text = 0
    endif

    Should this go in the page footer, report footer, or both? I'm assuming (mycontrol.text)= (RAI.CountOfOverride not to Lodge) or is it in my case, from my SQL above, [CountOfOverride not to Lodge]? Do I need []?
    Is it posible that the spaces in my field names is causing some issues.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    replace mycontrol with the name of the control that you are currently using in which ever footer section you are currently using, whether thats the report,page or group footer(s) I haven't got a clue, largely becuase you haven't said where you are using this

    alternatively you could use the NZ function

    again as to how you use that in your application I haven't got a clue... its your application not mine and I don't have it in front of me
    but lets assume you are using the recordset above

    =nz([CountOfOverride not to Lodge],0)
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    May 2014
    Posts
    35
    Provided Answers: 2
    The probem is that in the 4 stages of learning I'm in stage 1, Unconscious Incompetence; I don't know what I Don't know. I don't know what questions to ask and I don't know what info to provide. I appreciate healdem in his/her attempts to help me, but I sence he/she is just as frustrated as I am.

    In my report the test box with the control source, [CountOfOverride not to Lodge] from a query, is in the detail section of the report. I have placed all of the sugestions as an [Event Procedure] in the On Format event in the detail, page footer and report footer. I either get error messages or blank field in the report.

    What am i missing?

  9. #9
    Join Date
    Oct 2012
    Posts
    56

    Information

    Hi I have a report that has several sub reports with a textbox control that sums a group from the sub reports. This is a sample of my code which may give you an idea.

    Code
    --------------------------------------

    =IIf([RptShift-1].Report.HasData,[RptShift-1].Report!txtSalesExtTotal,0)

    End of Code
    -------------------------------------------------------------------



    When the sub report has no data it puts 0 the purpose in mine is I need a value in all sub reports to complete my calculation

    Hope this helps

    Note: I had help on this on this forum a long time ago not sure from who.

    Cheers Bob

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    =nz(columnfromableorquery,0)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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