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..
I'm not sure I understand what you are saying.. Heres some code of my form .. maybe this will help things out a bit..
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 & "# "
If Not (IsNull(Me.strArea)) Then
If sqlString <> "" Then sqlString = sqlString & " And"
sqlString = sqlString & " tblInspections.strArea Like '" & Me.strArea & "' "
If sqlString <> "" Then sqlString = " Where" & sqlString
sqlString = "SELECT tblInspections.strDate, tblInspections.strArea FROM tblInspections INNER JOIN tblQR ON (tblInspections.strArea = tblQR.strArea)" & sqlString & ";"
CurrentDb.CreateQueryDef "qryQualityStatus", sqlString
DoCmd.OpenReport "MyReport", acViewPreview
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
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..
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?
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...
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 & "' "
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])")
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 ...
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])")
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.
The user opens up the form--
On field Date Found: he puts in oct1 thru oct31
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)
On field Date Found: he puts in oct1 thru oct31
On site combo field he selects = 3331
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.