Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    292

    Unanswered: report record counting

    I posted this topic earlier.. but I am still confused as to how to solve the problem.. my problem is..

    I have a form which pulls records in for different areas.. The user enters in dates, the area they want to see records hit a button and a report comes up displays the information for that site and for those dates.
    My question is in between those dates are other records from other sites.. I want to know the difference of the records that are currently on the report and the records that are NOT on the report .. between the dates specified by the user..

    heres an example of what i mean...

    rec1 1/1/03 area1
    rec2 1/1/03 area1
    rec3 1/1/03 area2

    If the user asked for all records between 01/01/03 thru 01/30/03 AND "area1" then it would display

    2 records on this report
    1 record not on this report <~~ I want to know how do i get this number with my example..

    thanks

    Here's a link to my last post of this topic... the mdb is attached in the link.

    http://www.dbforums.com/t966294.html

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: report record counting

    Originally posted by TonyT
    I posted this topic earlier.. but I am still confused as to how to solve the problem.. my problem is..

    I have a form which pulls records in for different areas.. The user enters in dates, the area they want to see records hit a button and a report comes up displays the information for that site and for those dates.
    My question is in between those dates are other records from other sites.. I want to know the difference of the records that are currently on the report and the records that are NOT on the report .. between the dates specified by the user..

    heres an example of what i mean...

    rec1 1/1/03 area1
    rec2 1/1/03 area1
    rec3 1/1/03 area2

    If the user asked for all records between 01/01/03 thru 01/30/03 AND "area1" then it would display

    2 records on this report
    1 record not on this report <~~ I want to know how do i get this number with my example..

    thanks

    Here's a link to my last post of this topic... the mdb is attached in the link.

    http://www.dbforums.com/t966294.html
    I'm afraid to wallow in the past post and I don't have much time right now so I'll just suggest something here and see how it sounds to you.

    If you are gathering info from the form to display on the report couldn't you use a recordset to discover all of the information relating to the date range first. Store this and then use another recordset or the same one and filter it this time by the area. One subtracted from the other would be the count you are looking for right?

    Even if this approach won't work exactly, recordset manipulation is a great way to discover information such as this.

    Let me know.

    Gregg

  3. #3
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465

    Re: report record counting

    Originally posted by TonyT
    I posted this topic earlier.. but I am still confused as to how to solve the problem.. my problem is..

    I have a form which pulls records in for different areas.. The user enters in dates, the area they want to see records hit a button and a report comes up displays the information for that site and for those dates.
    My question is in between those dates are other records from other sites.. I want to know the difference of the records that are currently on the report and the records that are NOT on the report .. between the dates specified by the user..

    heres an example of what i mean...

    rec1 1/1/03 area1
    rec2 1/1/03 area1
    rec3 1/1/03 area2

    If the user asked for all records between 01/01/03 thru 01/30/03 AND "area1" then it would display

    2 records on this report
    1 record not on this report <~~ I want to know how do i get this number with my example..

    thanks

    Here's a link to my last post of this topic... the mdb is attached in the link.

    http://www.dbforums.com/t966294.html
    This query is can by help you, is from your base:
    SELECT Count(tblInspections.strProject) AS CuentaDestrProject, (SELECT Count(tblInspections.strProject) AS CuentaDestrProject
    FROM tblInspections
    HAVING (((tblInspections.strDate)>=#10/23/2003# And (tblInspections.strDate)<=#10/23/2003#))) AS valor
    FROM tblInspections
    HAVING (((tblInspections.strArea)="AREA 2") AND ((tblInspections.strDate)>=#10/23/2003# And (tblInspections.strDate)<=#10/23/2003#));
    the result are:
    first : 4
    second : 12
    in the same query.
    Saludos
    Norberto

  4. #4
    Join Date
    Jul 2003
    Posts
    292
    Actually,

    jmrSudbury and I have been working with this module FunCount5.. right now I'm trying to get the function to do this..

    First Get the records between dates specified on form

    Now,

    (Count - all REJ of Y AND DATECLOSE IS NULL) minus
    (Count - all REJ of Y AND DATECLOSE IS NULL AND SITE (Over here would be whatever site the user wants to see from combo box on form)

    Is there a way I can do that ?


    thanks to the both of you for responding


    Heres the function that we been working on

    Function funCount5() As Variant
    funCount5 = DCount("[INSP]", "[qryQualityStatus]", "[REJ] = 'Y' And IsNull([DATECLOSE])") - DCount("[INSP]", "[qryQualityStatus]", "[REJ] = 'Y' And IsNull([DATECLOSE])" And ([Site]))
    End Function

Posting Permissions

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