Query records from the current year, month and records with Null value

[new2access via AccessMonster.com]

i have this SQL code on my query to display training schedule records on the
current year and month..it works fine but i want also to query records with
NULL values on my date field (NTS_Month).

So the query should display all training schedule records from the current
year, current month and all records with null values because we want these
null records to schedule for a training.

Code:
SELECT Schedule.ID, Schedule.TraineeID, Schedule.NTS_Month, Trainees.LastName,
Trainees.FirstName, Trainees.JobTitle, Trainees.SupervisorContactNo
FROM Trainees INNER JOIN Schedule ON Trainees.TraineeID = Schedule.TraineeID
WHERE (((Year([NTS_Month]))=Year(Now())) AND ((Month([NTS_Month]))=Month(Now()
)));

In addition to current year+month+null values, can i query it with the
current date? so it will come current year+current month+CURRENT DATE+null
values. it will display records from this day up to end of this month.

Thanks!

 

[Allen Browne]

Try something like this:

SELECT Schedule.ID, Schedule.TraineeID,
Schedule.NTS_Month, Trainees.LastName,
Trainees.FirstName, Trainees.JobTitle,
Trainees.SupervisorContactNo
FROM Trainees INNER JOIN Schedule
ON Trainees.TraineeID = Schedule.TraineeID
WHERE (NTS_Month >= DateSerial(Year(Date()), Month(Date()), 1)
AND NTS_Month < DateSerial(Year(Date()), Month(Date())+1, 1))
OR NTS_Month Is Null;

Things to note:
a) The brackets matter when mixing ANDs and ORs.

b) The criteria are designed to perform better by using any index you have
on the date field. Details:
http://allenbrowne.com/QueryPerfIssue.html#CalcFields

c) If the field contains both a date and a time, records for the last day of
the month are included because we specified less than the first day of the
next month.

 

[new2access via AccessMonster.com]

Perfect!! that's what i need

Thanks Allen!

Try something like this:

SELECT Schedule.ID, Schedule.TraineeID,
Schedule.NTS_Month, Trainees.LastName,
Trainees.FirstName, Trainees.JobTitle,
Trainees.SupervisorContactNo
FROM Trainees INNER JOIN Schedule
ON Trainees.TraineeID = Schedule.TraineeID
WHERE (NTS_Month >= DateSerial(Year(Date()), Month(Date()), 1)
AND NTS_Month < DateSerial(Year(Date()), Month(Date())+1, 1))
OR NTS_Month Is Null;

Things to note:
a) The brackets matter when mixing ANDs and ORs.

b) The criteria are designed to perform better by using any index you have
on the date field. Details:
http://allenbrowne.com/QueryPerfIssue.html#CalcFields

c) If the field contains both a date and a time, records for the last day of
the month are included because we specified less than the first day of the
next month.

i have this SQL code on my query to display training schedule records on
the

[quoted text clipped - 21 lines]

Thanks!