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

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

--
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: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?!

Thank you in advance for your help.
--


Neb


.

Quantcast