PDA

View Full Version : Filter by day of the week query


ABC123

chrisdoane
09-08-2014, 01:36 PM
I have a date field I would like to use in a query to filter all records with a date that is on any Monday during the year. How would that filter look? So just filtering the dates that are Monday for the year.

Thanks in advance.

Chris

Stan Mathews
09-08-2014, 01:39 PM
dow(your_date_field_name) = 2 .and. year(your_date_field_name) = year(date())

chrisdoane
09-08-2014, 01:45 PM
So Does the 2 = the day of the week? IE:
Sun = 1
Mon = 2
Tue = 3
Wed = 4
Thur = 5
Fri = 6

Chris

chrisdoane
09-08-2014, 01:53 PM
It works without the year portion, but when I add:
.and. year(visitdate) = year(date()) = 2014
It gives me no pages to display message

Stan Mathews
09-08-2014, 02:03 PM
My post should have read

dow(your_date_field_name) = 2 .and. year(your_date_field_name) = year(date())

the = 2014 was copied and pasted in accidentally.

chrisdoane
09-08-2014, 02:27 PM
Thanks Stan, Worked great. Im going to go one level deeper, if you dont mind. If I wanted the query to ask me with day of the week I want to filter, how would that look? So when My report runs it would ask: "What Day" and I would like to be able to type "Monday". That Possible?

Stan Mathews
09-08-2014, 04:00 PM
Possible but the best way is not for the report to ask you for the day of the week.

Usually a button is set up on a form. The form has a variable defined and shown on it. The user makes a choice for the variable and then presses the button. The button calls the report and passes the variable to the report filter.

I'll try to work up a sample for you if I have time.

Stan Mathews
09-08-2014, 04:25 PM
Try this.