Re: Report doesn't show all records between dates

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Something like this:

PARAMETERS
[Forms].[form_CounsellorsTutorsStudents_ParFields]![StartDate] DateTime,
[Forms].[form_CounsellorsTutorsStudents_ParFields]![EndDate] DateTime;
SELECT Match.Match_ID, Match.ClassType, Match.Counsellor,
Match.Status, Match.StartDate, Match.EndDate,
Match.Tutor_ID, Match.TutorNumber, Match.T_FirstName,
Match.T_LastName, Match.Student_ID, Match.StudentNumber,
Match.S_FirstName, Match.S_LastName, Match.Location, Match.
ReasonMatchEnded
FROM [Match]
WHERE ((([Forms]![form_Students_ParFields]![Counsellor] Is Null)
OR (Match.Counsellor =
[Forms]![form_Students_ParFields]![Counsellor]))
AND (([Forms].[form_CounsellorsTutorsStudents_ParFields]![StartDate] Is Null)
OR (Match.StartDate >=
[Forms].[form_CounsellorsTutorsStudents_ParFields]![StartDate]))
AND (([Forms].[form_CounsellorsTutorsStudents_ParFields]![EndDate] Is Null)
OR (Match.StartDate <
[Forms].[form_CounsellorsTutorsStudents_ParFields]![EndDate] + 1)))
GROUP BY Match.Match_ID, Match.ClassType, Match.Counsellor,
Match.Status, Match.StartDate, Match.EndDate,
Match.Tutor_ID, Match.TutorNumber, Match.T_FirstName,
Match.T_LastName, Match.Student_ID, Match.StudentNumber,
Match.S_FirstName, Match.S_LastName, Match.Location,
Match.ReasonMatchEnded;

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Neb" <Neb@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A6B428C8-51BF-4227-8296-FBF6E89BD94E@xxxxxxxxxxxxxxxx
Hello Allen,
I have added the statements into Parameter list, on query. This is the SQL:

SELECT Match.Match_ID, Match.ClassType, Match.Counsellor, Match.Status,
Match.StartDate, Match.EndDate, Match.Tutor_ID, Match.TutorNumber,
Match.T_FirstName, Match.T_LastName, Match.Student_ID, Match.StudentNumber,
Match.S_FirstName, Match.S_LastName, Match.Location, Match.ReasonMatchEnded
FROM [Match]
GROUP BY Match.Match_ID, Match.ClassType, Match.Counsellor, Match.Status,
Match.StartDate, Match.EndDate, Match.Tutor_ID, Match.TutorNumber,
Match.T_FirstName, Match.T_LastName, Match.Student_ID, Match.StudentNumber,
Match.S_FirstName, Match.S_LastName, Match.Location, Match.ReasonMatchEnded
HAVING (((Match.Counsellor) Like
Nz([Forms]![form_Students_ParFields]![Counsellor],"*")) AND
((Match.StartDate) Between
Nz([Forms].[form_CounsellorsTutorsStudents_ParFields]![StartDate],#1/1/1900#)
And Nz([Forms].[form_CounsellorsTutorsStudents_ParFields]![EndDate],Date())));

Would you please help me incorporate properly your suggestion with Where
couse into the SQL SELECT? Thanks.
--
Neb


"Neb" wrote:

Thank you very much Allen. I'll let you know my feedback later.

Neb
--
Neb


"Allen Browne" wrote:

> Switch the query to SQL View (View menu, when in query design.)
>
> In the query statement you will see a WHERE clause that looks something > like
> this:
> WHERE Table1.StartDate Between
> Nz([Forms]![f_Students_Filter]![StartDate]) And
> Nz([Forms]![f_Students_Filter]![EndDate],Date())
>
> Change it to this kind of thing:
> WHERE (([Forms]![f_Students_Filter]![StartDate] Is Null)
> OR (Table1.StartDate >= [Forms]![f_Students_Filter]![StartDate]))
> AND (([Forms]![f_Students_Filter]![EndDate] Is Null)
> OR (Table1.StartDate < [Forms]![f_Students_Filter]![EndDate] + 1))
>
> This works since it directly tests the text box on the form for Null > and
> evalutates to True in that case (instead of comparing it to your > field.) If
> the text box is not null, the criteria only evaluates to True if the > date
> field is greater than or equal to the field value. Same approach for > the
> ending date.
>
> To ensure Access understands the text box values as dates, it would > also be
> a good idea to declare them. In query design view, choose Parameters on > the
> Query menu, and enter to rows into the dialog:
> [Forms]![f_Students_Filter]![StartDate] Date/Time
> [Forms]![f_Students_Filter]![EndDate] Date/Time
>
> If you have lots of these optional criteria boxes on your form, it will > be
> more efficient to build the WhereCondition/Filter string from only the > boxes
> where the user enters something. Details in:
> Search form - Handle many optional criteria
> at:
> http://allenbrowne.com/ser-62.html
>
> "Neb" <Neb@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:A600EAB2-6E0E-423E-97B3-1BF408102B01@xxxxxxxxxxxxxxxx
> >I have query with two date fields: StartDate and EndDate, filter form > >with
> > two unbound text boxes, StartDate and EndDate, and report sorted on
> > StartDate, group on Month, Keep Together: Whole Group.
> > What I am trying to do is to get a total # of students grouped by > > sex:
> > Female and Male for the selected period of time on filter form. More > > info:
> > - Query, StartDate, Criteria: Between
> > Nz([Forms]![f_Students_Filter]![StartDate]) And
> > Nz([Forms]![f_Students_Filter]![EndDate],Date())
> > - Filter form, OK button, Click: Me!.Visible = False
> > - Report, text box in StartDate Header, control source:
> > =Format([StartDate],"mmmm") & ", " & Year([StartDate]), text box Sex > > in
> > StartDate Header, and text box in StartDateHeader, control source:
> > =Count([Student_ID])
> > - Page Header, text box, control source ="You have selected dates > > from " &
> > (Forms!f_Students_Filter!StartDate) & " to " &
> > (Forms!f_Students_Filter!EndDate)
> >
> > When I run report, enter StartDate and EndDate, and click on OK, it's
> > showing name of the month and year, sex and count, but not taking all
> > record.
> > It's only taking records with StartDate for the certain month. I want > > to
> > include records with EndDate, and empty date on EndDate field
> > (continuing).
> >
> > Is that because I am using only StartDate?!

.

Quantcast