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

  • Thread starter new2access via AccessMonster.com
  • Start date
N

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!
 
A

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.
 
N

new2access via AccessMonster.com

Perfect!! that's what i need :)

Thanks Allen!



Allen said:
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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top