Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Join Date
    Jul 2003
    Posts
    292

    Unanswered: Report Counting...

    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

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    The first one is easy:
    =Sum(1) & " records are included ..."

    The second one is more difficult, but you could use a dcount and subtract out the result from the first box:

    =dcount("*","tableORQueryName") - sum(1)

    If this does not work, then move the Sum(1) to it's own field and reference it by name:

    =dcount("*","tableORQueryName") - theSum

  3. #3
    Join Date
    Jul 2003
    Posts
    292
    I'm not sure I understand what you are saying.. Heres some code of my form .. maybe this will help things out a bit..

    Code:
    Private Sub Command36_Click()
    Dim sqlString As String
    sqlString = ""
    
    If Not (IsNull(Me.txtStartOpen) Or IsNull(Me.txtStartEnd)) Then
    sqlString = " tblInspections.strDate Between #" & Me.txtStartOpen & "# And #" & Me.txtStartEnd & "# "
    End If
    If Not (IsNull(Me.strArea)) Then
    If sqlString <> "" Then sqlString = sqlString & " And"
    sqlString = sqlString & " tblInspections.strArea Like '" & Me.strArea & "' "
    End If
    
    If sqlString <> "" Then sqlString = " Where" & sqlString
    sqlString = "SELECT tblInspections.strDate, tblInspections.strArea FROM tblInspections INNER JOIN tblQR ON (tblInspections.strArea = tblQR.strArea)" & sqlString & ";"
    
    Debug.Print sqlString
    CurrentDb.QueryDefs.Delete "qryQualityStatus"
    CurrentDb.CreateQueryDef "qryQualityStatus", sqlString
    
    DoCmd.OpenReport "MyReport", acViewPreview
    Form_frmreportQualityStatus.Requery
    
    End Sub
    thanks for your response

  4. #4
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    On the report make a text box with the following control source:

    =Sum(1) & " records are included ..."

    You could create a second text box with the following control source:

    =dcount("*","tableORQueryName") - sum(1)

    If this does not work, then move the Sum(1) to it's own textbox and reference it by name:

    =dcount("*","tableORQueryName") - theSum

  5. #5
    Join Date
    Jul 2003
    Posts
    292
    I'm still a little confused.. let me start from scratch with different examples:

    In my table I have records as follows:
    record rej open close area
    rec1 Y 7/01/03 07/05/03 area1
    rec2 Y 7/01/03 area2
    rec3 N 7/05/03 07/06/03 area1
    rec4 Y 7/05/03 area1
    rec5 N 7/05/03 area1

    Ok on my form the user enters in dates... ie..

    07/01/03 thru 07/30/03

    This would return all dates with a REJ of Y

    Result on this report:3
    Results NOT on this report : 0

    Now if the user comes back to the form.. enters in

    07/01/03 thru 07/30/03
    AND
    area: area1

    This would bring back all dates with Y and area1

    Results on this report:2
    REsults NOt on this report :1

    I have close dates on these records because.. all records with a REJ of Y have to be closed.. so they run reports... of all outstanding records that have a rej of Y and are not closed.. so that way they can go back find which ones are not closed and go back and close them...

    I dont know if this helped out any.. if you would like to see the mdb.. i'll attach it.. and hopefully this will explain things better..
    Last edited by TonyT; 11-11-03 at 14:38.

  6. #6
    Join Date
    Jul 2003
    Posts
    292
    the form im working on is.. frmreportQualitystatus
    the report its going to is.. myreport
    the table its uses are tblinspections
    and query is qryQualityStatus..



    thanks

  7. #7
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Now I am confused. In your original post, you said, " I want to know the difference of the records that are currently on the report and the records that are NOT on the report." Did you want these counts to show on the report or on the form. I gave instructions for putting them on the report. If you want them on the form, then you will have to do dcount's. Make a field that has a control source of:

    =dcount("*","qryQualityStatus","strDate between ... ")

    another field counting the opposite where clause should give you the data you are looking for.

    Are the dates hardcoded in the query for testing purposes only?

  8. #8
    Join Date
    Jul 2003
    Posts
    292
    Actually yes.. I do want this in the report.. my form is there just to do searches for records..

    each field in the form is there to display what the user wants to see on the report..

    Ok heres what the user would see running through the program....

    first on the form.. "frmreportQualityStatus"

    If you enter in dates.. 10/01/03 thru 10/30/03 .. in date found
    and press submit.. you get a Quality Status Report... in the report.. the first column there..

    Times performed - Here it searches in my field INSP the first two letters.. like say in the field INSP there are DR1,DR2,DR.. this would count 3 for Design Review.. SO1,SO,SO3,SO5 would count 4 for Soil

    Number noncnfrm - Here out of all records specified in date above (oct-1 thru oct 30) It displays a count of all records, say column Design Review has 3 DR ( from column Times Performed), That have DR and a REJ of "Y" (REJ = Rejection yes or no) -- So here it would count all records that have a DR and a REJ of Y...

    Number Closed - Here out of all records with a DR AND REJ of "Y".. AND has a date in the "DATECLOSED" field display a count of these records..

    so ideally you want Number Noncnfrm to equal Number Closed on the report.. say that you have 5 on Number Noncnfrm and 4 on Number closed.. you would have 1 record outstanding..

    Everything here runs fine..

    but if you look at the bottom right corner of my report ..

    I need one more calculation.. the first says

    outstanding on this report.. ( which displays all records on the current report)
    - if you go back to my form.. theres a combo box there that says site. The user usually will click that and choose which site they want to see reports in.. soo which brings us back to the report..

    If the user selects between dates.. and then chooses the site it only displays records between those dates and that site on the report.. but ommits the other records NOT on that site but ARE between those dates..

    I need to display how many are outstanding that are NOT on the current report but between those dates.. and display that on Outstanding Noncnfrm NOT on this report...


    I hope i explained things a little better..

  9. #9
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Just use the dcount. Are you having trouble with the Where part of the dcount?

    So if the user specifies dates, the Not included should be 0 and when an area is chosen, the Not Included should count where
    "strdate between #" & me!date1 & "# and #" & date2 & "# and area <> '" & me!specifiedarea & "' "

  10. #10
    Join Date
    Jul 2003
    Posts
    292
    I'm sorry I'm still confused to what you are saying.. I'm rarely new if you could explain in some detail.. I would appreciate it alot.. thanks so much for your time...

  11. #11
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Try this function to get the total then subtract out your other value:

    Function funCount5(prmSearchString2 As String) As Variant
    funCount5 = DCount("[INSP]", "[qryQualityStatus]", "[REJ] = 'Y'") - DCount("[INSP]", "[qryQualityStatus]", "[REJ] <> 'Y' And Not IsNull([DATECLOSE])")
    End Function
    Last edited by jmrSudbury; 11-06-03 at 00:25.

  12. #12
    Join Date
    Jul 2003
    Posts
    292
    I added the function to my mod.. and in my report.. In my text box I added the code..
    Code:
    =funCount5([INSP])
    and it displayed the wrong number..

    Did I write that wrong in the report ?

  13. #13
    Join Date
    Jul 2003
    Posts
    292
    This is how i tested it...

    I opened my form.. put in dates 10/01/03 thru 10/31/03 in my Date Found fields.. hit submit and got 3 outstanding on this report.. and it says 3 outstanding not on this report... should it read.. 3 outstanding on this report and 0 outstanding NOt on this report ?

    I take the same dates.. and under the site field.. I put in site 3331

    It now reads 0 outstanding on this report..
    and 1 outstanding not on this report ...

    shouldnt it read 0 and 3 ?

  14. #14
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    You can try this and stop the INSP variable from being passed.

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

    Is this right? Is it true that you want a count where rej is Y and dateClose is not null less a count where rej is not Y and DateClose is not null? If this is not what you want, then tell me exactly what you do want. Tell me what has te be counted under what circumstances.

  15. #15
    Join Date
    Jul 2003
    Posts
    292
    Ok consider I have these records...
    Records.....Open Date............Close Date.......REJ............SITE
    ============================================
    rec1..............oct2.....................oct5... .........Y..............3331
    rec2..............oct3............................ ...........Y..............3331
    rec3..............oct4.....................oct7... .........N..............3331
    rec4..............oct5............................ ...........Y..............4401
    rec5..............oct6............................ ...........Y..............4401
    First Run:
    The user opens up the form--
    On field Date Found: he puts in oct1 thru oct31
    hits submit...

    output
    -------
    Record Outstanding On this report = 3
    Record Outstanding NOT on this report = 0

    (So here I think its all records with a "REJ" of Y and "DATECLOSE" IS NULL)

    Second Run:
    Opens Form
    On field Date Found: he puts in oct1 thru oct31
    On site combo field he selects = 3331
    hits submit..

    output
    -------
    Record Outstanding On this report = 1
    Record Outstanding NOt on this report = 2
    (record outstanding on and not on this report should equal the total of first run - since there are no "site" check)

    (Here I think its checking all "REJ" of Y and "DATECLOSE" IS NULL and "Site" = 3331 or whatever the user selects as site to check.

    So really i think its ((REJ=Y, DATECLOSE IS NULL) - (REJ=Y, DATECLOSE Is NULL AND SITE = whatever the user selects on this field))


    ALso on the report.. in the text box.. you said stop passing INSP.. do you mean.. just put.. "=[funCount5]" in the txt box ?


    thanks for all your help.. it was confusing to me too at first when it was given to me.
    Last edited by TonyT; 11-06-03 at 12:28.

Posting Permissions

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