Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2004
    Posts
    41

    Unanswered: Counting Specific Rows In a Report!

    Hello.

    I have a table which contain names and dates identifying events, plus, a tickbox which identifies if the event is valid or not:

    | name_one | date_one | invalid_one | ... ...

    I use reports for a long time now, and it's printing out great.
    Now, here is what I want to do-

    I display all dates, whether they are valid or not. I would like to add, in the end footer of the report, a count which will write how many VALID dates I have. So if we have 5 rows, and two are checked under invalid, I would like it to display: "Valid Dates: 3".

    I have no idea of how I can run a query, and display it's result. knowing that, will solve this issue for me, as I know how to write this query.

    The thing is, I don't know how to run a query, and then write the results on the report, how do I call query outputs from a report?

    Thank you all in adavnce, it's very important and urgent for me, can't thank you enough!

  2. #2
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    No one around so let me see if I can help you.

    If you're not too worried about performance, you could use DCount.

    It would look like this:

    =DCount("[Name]", "yourquerynamehere", "[Invalid] = 0")

    This will count names from a query (or table) where Invalid is false. If you need to check for valid being true then the last argument would be
    "[Valid] = -1"

    To display the results in an existing report, you can create a subreport. Use the query as the record source of that subreport.

    I've made a few assumptions here, but hopefully this helps you.

    -Chris

  3. #3
    Join Date
    Dec 2004
    Posts
    41
    Thank you very much!

    I will try both your solution and subreport theory!

    Another small thing, If I want to display a certain value returned from a query, how can I do it?

    I don't care about performances at all, I just need things to be done.

    Again, Thank you!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd suggest that you approach it slightly differently

    define a global variable in the report
    set it to zero in the group header
    test the date in the detail format event
    set you counters accordingly
    ie if isdate([datevar]) then intNoDates=intNoDates+1

    in the group footer format event transfer the value to an unbound control

    it may seem a long winded way of doing it, but you do have ultimate flexibility as to how you run your counters, It aslo gets rid of having to use the domain functions.

  5. #5
    Join Date
    Dec 2004
    Posts
    41
    I can't thank you enough guys. I got everything working now and i'm super happy, thank you !!

    I have another slight issue, I hope i'm not getting on your nerves here

    I'm using a simple query to get rows between two dates, the dates in my database are formatted as : dd/mm/yyyy.

    The query goes, theoretically: select all, from table, where bla bla bla, BETWEEN #01/11/2004# and #30/11/2004#.

    The problem is, I got both dates setup just fine, but when the query is created, the first date is getting swapped, instead of 01/11, I get 11/01.. it's like it's confusing between the dd and mm. I format the dates, I msgbox the variables and I get the correct dates, I tried and did everything, but when the query is created, it's always the wrong date format.

    Do I have to convert all my dates into mm/dd/yyyy, or there is a simple solution for this somewhere?

    THANKS AGAIN FOR YOUR HELP.

  6. #6
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi SDawn and "Welcome to the Forum"

    If you have a field that is a CheckBox and you want to Count when that box is Checked (True), then in your ReportFooter do something like this.

    You have a CheckBox named Valid which is placed beside a Date field.
    In your ReportFooter place an Unbound TextBox and place this inside of it or it's ControlSource line:
    Code:
      =Abs(Sum([Valid])) 
    That will give you a Total of all the CheckBoxes that are Checked as True being ValidDates.

    Hope this gets you what you want and have a Happy Holiday.
    BUD

  7. #7
    Join Date
    Dec 2004
    Posts
    41
    thanks bud, I got it all working

    My big problem now is the date one, read about it in my last post, can you help me with that?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you copy of Access (Office) and operating system is correctly localised then you should be able to quote dates without using the "#", however it seesm odd to me to use a hardcoded date

    you could try 01 nov 2004 in place of 01/11/2004
    or possibly
    cdate("01 nov 2004") in place of 01/11/2004

  9. #9
    Join Date
    Dec 2004
    Posts
    41
    I tried your idea of removing the #'s, and it did seem to work, although now it displays 1/11/2004, and not 01/11/2004, and the format is dd/mm/yyyy, so that little zero in the front is my problem right now

    Ideas?

Posting Permissions

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