Only show records that meet criteria

[RussH]

I want to run a simple report based on checkbox criteria. there are 4 fields on the report-



Student (text), BRFID (PK), Date (SD), PRN (checkbox) and the report is grouped on student. I want to show only students that have 4 checkboxes marked true/yes in the last 14 days.

I got the last 14 days working but can figureout how to show just the students that have at least 4 true/yes. If no student meets the criteria, I would like a msg box appear stating "No student meets the criteria" and close the report.

This has probably been asked and answered already so just give me a nudge in the right direction.

Thanks for your time

[June7]

Criteria based on data in other records of same table can be tricky. If you want to see detail info such as dates and primary key, requires subquery or domain aggregate function.

Domain Aggregate approach>
Create field with expression:
CountPRN: DCount("*","tablename","Student='" & [Student] & "' AND PRN=True AND <the 14-day criteria must also be included here>")
Criteria under that constructed field:
>=4

Query approach>
Build an aggregate query that includes the date range criteria.
SELECT Student, Sum(IIf(PRN=True,1,0)) AS CountPRN FROM tablename GROUP BY Student ...;
Join that query in the report RecordSource, apply criteria to the CountPRN field of >=4.

[RussH]

Sorry for the late reply-got pulled to another project. I tried to use the query approach and using the code you suggested it worked like a charm. I got back 3 students with the count. I then added the field "maindate" with the criteria of between now() and now() - 100 ( I used 100 instaed of 14 in my original post so I could get some data) and I should of got the same results but I get nothing

Thank you for your time

[June7]

Post the exact queries you attempted.

[RussH]

SELECT tblBRFMain.Student
FROM tblBRFMain
GROUP BY tblBRFMain.Student
HAVING (((Sum(IIf([PRN]=True,1,0)))>=4));

This is the SQL staement. when I run the query it returns 3 students with a sum of how many PRN = True. this is works perfect-Thank you

I want to be now be able to get only the results between now - 14 days that meet the criteria of >=4.

There is a field in this table name maindate. I added it to the qry and put in the criteria section "between now() and now() -100. Below is the SQL for the change I made.


SELECT tblBRFMain.Student, tblBRFMain.MainDate
FROM tblBRFMain
GROUP BY tblBRFMain.Student, tblBRFMain.MainDate
HAVING (((Sum(IIf([PRN]=True,1,0)))>=4) AND ((tblBRFMain.MainDate) Between Now() And Now()-100));

Thank you in advance

[June7]

If any of the records are outside the date range then the Sum won't capture them and the >=4 criteria will not be met. That's the only explanation I can come up with without examining data.

[RussH]

I have attached a sample DB with data. I left the query in the state it works without try to filter on date ranges. I also noticed that there was nothing that meet the now -100 critera (thank you for pointing that out) but I changed it to now - 400 and still got no results.

[June7]

I just noticed you have the MainDate field in the GROUP BY clause. Change to WHERE under the MainDate field.

I get two Records (AF & EB) with:

SELECT tblBRFMain.Student
FROM tblBRFMain
WHERE (((tblBRFMain.MainDate) Between Now() And Now()-400))
GROUP BY tblBRFMain.Student
HAVING (((Sum(IIf([PRN]=True,1,0)))>=4));

[ssanfu]

Also, be aware that using Now() could give you different results than using Date().
Now() is a date and time
Date() is just a date. To be more specific, it is a date with the time component always set to 00:00:00

Try this:
Open the IDE immediate window (ctrl-G)
type
? Date()
hit enter

type
? Now()
hit enter

type
? format(Date(),"m/d/yyyy hh:nn:ss")
hit enter

type
? format(Now(),"m/d/yyyy hh:nn:ss")
hit enter

Lets say on 4/1/2013 08:030AM you entered a record for Tony. ("PRN" is TRUE.)
So on 4/15/2013 at 2pm (1400hrs) you run your query.
Since you are using Now(), the criteria will be (Between Now() and Now() - 14)
Between 4/1/2013 14:00:00 and 4/15/2013 14:00:00

But Tony's record was entered at 4/1/2013 8:30AM. So it will not be included. (I think it should have been selected)

If the criteria was "Between Date() and Date() - 14", Tony's record on 4/1/2013 8:30AM would be included
(Between 4/1/2013 00:00:00 and 4/15/2013 00:00:00)

Just something to be aware of.....

[RussH]

June7- your query modification worked PERFECT! Thank you for all of your time.
Steve- I did what you suggested and I changed the Now() to Date(). I ran the 2 across different date ranges and yes-there were instances of the results being different (now() having less returned results than Date() ).

I really appreciated your suggestion because the report based on this query goes to the State level and being off only 1 in the reporting brings down...
well, draw your on counclusions.

This was my first post and the help, suggestions, and professionalism was a breath of fresh air.

Thak you again guys for all of your help. Now have to figure out how to make this solved