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

You are right. Is there any other way of doing this? Thanks.
--
Neb


"Allen Browne" wrote:

If:
- your table contains a StartDate and an EndDate, and
- your parameters cover a StartDate and an EndDate, and
- you want the records where this is any overlap,
then the logic is like this:
A starts before B ends, and
B starts before A ends.

Craft your criteria to do that. It does get more messy where either end date
can be null, and even more messy if the startdates can be null as well.

--
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:385ECC8F-7BE8-4101-BD9D-BACB1A161DF0@xxxxxxxxxxxxxxxx
It’s working very well. Thanks a lot, Allen.

In another report, which is the total number of students female and male,
in
selected period of time – StartDate and EndDate - I see the problem with
taking correct records between selected StartDate and EndDate. In fact,
the
query is taking only correctly students' records with StartDate, for
example
from 1/1/2006 to 12/31/2006 - query is taking all records with StartDate
between these dates - BUT NOT all with EndDate between these two selected
dates. Some students started before this period and ENDED in this period,
but
because the query only taking correctly StartDate – they are not showing.
As
a result, a total number of students grouped by month in that period is
not
correct- Missing these students' records who are started before this
period
and were active or ended in this period. The present SQL is query is this:

PARAMETERS [Forms].[f_Students_Filter]![StartDate] DateTime,
[Forms].[f_Students_Filter]![EndDate] DateTime;
SELECT Match.Match_ID, Student.Sex, Match.Status, Match.StartDate,
Match.EndDate, Match.Student_ID, Match.StudentNumber
FROM [Match] LEFT JOIN Student ON Match.Student_ID = Student.StudentID
WHERE ((([Forms].[f_Students_Filter]![StartDate]) Is Null) AND
(([Forms].[f_Students_Filter]![EndDate]) Is Null)) OR
((([Forms].[f_Students_Filter]![EndDate]) Is Null) AND
((Match.StartDate)>=[Forms].[f_Students_Filter]![StartDate])) OR
((([Forms].[f_Students_Filter]![StartDate]) Is Null) AND
((Match.StartDate)<=[Forms].[f_Students_Filter]![EndDate]+1)) OR
(((Match.StartDate)>=[Forms].[f_Students_Filter]![StartDate] And
(Match.StartDate)<=[Forms].[f_Students_Filter]![EndDate]+1))
GROUP BY Match.Match_ID, Student.Sex, Match.Status, Match.StartDate,
Match.EndDate, Match.Student_ID, Match.StudentNumber
ORDER BY Match.StartDate;

Any help? Thanks in advance.

--
Neb


"Allen Browne" wrote:

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