Query for Month and Day against Date()

[Terri]

I am trying to extract records based on the month and day being today's date.
I created two separate fields in the table, one for month (MonthofYear) and
one for the day (DayofYear).

I am unable to restrict the records in the query. No matter what I have
tried the records containing a value in these fields are always selected no
matter what the values are.

This is a portion of query. Any help is greatly appreciated.

WHERE (((tblDutyOfficerTasks.DayShift)=Yes) AND
((tblDutyOfficerTasks.MonthofYear)=Month(Date())) AND
((tblDutyOfficerTasks.DayofYear)=Day(Date()))) OR
(((tblDutyOfficerTasks.DayShift)=Yes) AND
((tblDutyOfficerTasks.DayofWeek)=Format(Date(),"dddd"))) OR
(((tblDutyOfficerTasks.DayShift)=Yes) AND
((tblDutyOfficerTasks.DayofWeek)="Every Day")) OR
(((tblDutyOfficerTasks.DayShift)=Yes) AND
((tblDutyOfficerTasks.DayofWeek)="Weekdays Only") AND
((Weekday(Date()))="2")) OR (((tblDutyOfficerTasks.DayShift)=Yes) AND
((tblDutyOfficerTasks.DayofWeek)="Weekdays Only") AND
((Weekday(Date()))="3")) OR (((tblDutyOfficerTasks.DayShift)=Yes) AND
((tblDutyOfficerTasks.DayofWeek)="Weekdays Only") AND
((Weekday(Date()))="4")) OR (((tblDutyOfficerTasks.DayShift)=Yes) AND
((tblDutyOfficerTasks.DayofWeek)="Weekdays Only") AND
((Weekday(Date()))="5")) OR (((tblDutyOfficerTasks.DayShift)=Yes) AND
((tblDutyOfficerTasks.DayofWeek)="Weekdays Only") AND ((Weekday(Date()))="6"))

Thank you,
Terri

 

[Boyd Trimmell aka HiTechCoach]

For readability I reformatted you SQL

WHERE

(((tblDutyOfficerTasks.DayShift)=Yes) AND
((tblDutyOfficerTasks.MonthofYear)=Month(Date())) AND
((tblDutyOfficerTasks.DayofYear)=Day(Date())))

OR

(((tblDutyOfficerTasks.DayShift)=Yes) AND
((tblDutyOfficerTasks.DayofWeek)=Format(Date(),"dddd")))

OR

(((tblDutyOfficerTasks.DayShift)=Yes) AND
((tblDutyOfficerTasks.DayofWeek)="Every Day"))

OR

(((tblDutyOfficerTasks.DayShift)=Yes) AND
((tblDutyOfficerTasks.DayofWeek)="Weekdays Only") AND
((Weekday(Date()))="2"))

OR

(((tblDutyOfficerTasks.DayShift)=Yes) AND
((tblDutyOfficerTasks.DayofWeek)="Weekdays Only") AND
((Weekday(Date()))="3"))

OR

(((tblDutyOfficerTasks.DayShift)=Yes) AND
((tblDutyOfficerTasks.DayofWeek)="Weekdays Only") AND
((Weekday(Date()))="4"))

OR

(((tblDutyOfficerTasks.DayShift)=Yes) AND
((tblDutyOfficerTasks.DayofWeek)="Weekdays Only") AND
((Weekday(Date()))="5"))

OR

(((tblDutyOfficerTasks.DayShift)=Yes) AND
((tblDutyOfficerTasks.DayofWeek)="Weekdays Only") AND
((Weekday(Date()))="6"))



If a record matches any of the criteria between on on the "or" then it will
be returned.

Are you wanting just the record for a single day?

From your WHERE criteria, I can not really figure out how you would
calculate this.

Also, how are you handling the year part?

 

[Terri]

Thanks for responding. Sorry about the formatting. I will keep that in
mind. It is really just the first set of criteria that won't work. The rest
pick up the proper records.

The table is a list of tasks that are assigned to some or all shifts, hence
DayShift.

They are also assigned to a day of the week, Sunday, Monday, Tuesday etc.,
Weekdays Only or Every Day which the rest of the crtieria matches.

There are also tasks assigned to a certain date, e.g. Jan. 1 of every year.
I set up two fields to handle this. I am not concerned with the year but
want to match the month (an integer value such as 11) to the current date and
the same for the day.

I am trying to use the first set of criteria to filter these records but no
matter what the values are, (month = 7 and day = 15) all of the records with
or without data in these two fields are pulled into the query.

I hope this explains things better.