How to Use Navigation Where Clause
MS Access 2010 and newer version provide a Navigation Form that can display the form or subform under each navigation button (tab). Under each navigation button, there are three main functions under the Data tab of Property Sheet: Navigation Target Name, Navigation Where Clause, and Enabled. We normally select the form or subform for the Navigation Target Name to display form or subform for each Navigation button. In this How To, I will show the example of how to use the Navigation Where Clause.
Benefit of Using Navigation Where Clause
The Navigation Where Clause is the criteria for the target form. Many buttons can target to one form or subform. For example, I have four Navigation buttons as shown below. All four buttons relate to the information from task table. So I only create one task datasheet form, but will use for all four Navigation buttons. I will put different criteria under the Navigation Where Clause of each button. By using the Navigation Where Clause, you don’t have to create many queries or many datasheet forms. You will also reduce the space of your program.
For example, I have only one datasheet form frmTask_DS as shown below.
Button #1: All Tasks
I want this All Tasks button to display all tasks from the TaskDue table. Under the form design view, I select frmTask_DS form for the Navigation Target Name under the Data tab of the Property Sheet of All Tasks Navigation button and I will leave the Navigation Where Clause blank. I don’t need to put any criteria for Navigation Where Clause since I want to show all records from the table.
Result of All Tasks
Button #2: Task Completed
I want to show only the tasks that completed or have a FinishDate in the table under this Task Completed button. I will use same frmTask_DS form as a targeted form, but will put the criteria to show only the completed tasks. The completed tasks mean the tasks that have the date entered (not blank) under the FinishDate field.
Navigation Where Clause = FinishDate Is Not Null
Result of Task Completed
Button #3: Task Overdue
For this example, I want to show only the overdue tasks that have not finished and passed the due date under this Task Overdue button. I will use same frmTask_DS form as a targeted form, but will use different criteria to show only the overdue tasks.
Navigation Where Clause = FinishDate Is Null And DueDate < Date()
Result of Task Overdue
Button #4: Task Due this Month
For this example, I want to show only the tasks that have the due date in this month. The targeted form will be the same frmTask_DS form, but has different criteria. I will not use only the Month() function, but also use the Year() function to limit only this month of this year.
Navigation Where Clause = Year([DueDate]) = Year(Now()) And Month([DueDate]) = Month(Now())