Any other way? Than implementing the logic? No, I don't think you can
achieve it by something like swearing at the computer?
It's not that hard. Just work through the fact that one start date has to be
before the other end date, and vice versa.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
You are right. Is there any other way of doing this? Thanks.
--
Neb
:
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.
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
:
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
Reply to group, rather than allenbrowne at mvps dot org.
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
:
Thank you very much Allen. I'll let you know my feedback later.
Neb
--
Neb
:
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
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,
