Filter a repeating table on a date range using rules and filters

Use rules and filters to filter Microsoft Access database table records being shown in a repeating table on a date range using two date pickers.

ADVERTISEMENTS

Problem

You want to query a range of dates using two date pickers to return records from a Microsoft Access database table that fall between the two dates specified.

If you are looking to filter a repeating table that is bound to a SharePoint list, you can refer to this InfoPath 2010 video tutorial.

Solution

Use rules, filter expressions, the concat() function, and the substring() function to enable querying by a date range within InfoPath.

Discussion

You can accomplish this functionality as follows:

  1. Create a Table with the name Task and the following structure in a Microsoft Access database:
    Field Name Data Type
    ID (Primary Key) AutoNumber
    Name Text
    DueDate Date/Time
  2. Create a Query in Microsoft Access with the name TasksSortedByDate and the following SQL statement:
    SELECT Task.ID, Task.Name, Format(Task.DueDate,"yyyy-mm-dd") AS MyDueDate
    FROM Task
    ORDER BY Task.DueDate;
  3. Create a new InfoPath form and choose New From Data Connection... when creating the form.
  4. Set up the Data Connection to the Microsoft Access database and bind the form to the TasksSortedByDate query.
  5. Delete the table and all the controls that InfoPath has added to the form.
  6. Go to the Data Source pane.
  7. Add a Field (element) of type Date (date) to the Main data source of the InfoPath form and name it startDate.
  8. Add another Field (element) of type Date (date) to the Main data source of the InfoPath form and name it endDate.
    Your Main data source should now resemble the following figure:

    Figure 1. Main data source of the InfoPath form.
  9. Add a Table with Title layout table to the InfoPath form.
  10. Drag the startDate and endDate fields from the Data Source pane and drop them onto the form within the table.
  11. Drag the d:TasksSortedByDate node under the dataFields section from the Data Source pane and drop it onto the form as a Repeating Table within the layout table.
  12. Right-click on the MyDueDate field within the Repeating Table and change the control into a Date Picker control.
  13. Modify the table and controls to resemble the following figure:

    Figure 2. The InfoPath form in design mode.
  14. Double-click on the Repeating Table to open its Properties dialog box.
  15. Click on the Display tab and then on the Filter Data... button to open the Filter Data dialog box.
  16. Click on the Add... button on the Filter Data dialog box to open the Specify Filter Conditions dialog box.
  17. Select The expression from the first drop-down list box.
  18. Type in the following expression in the text box for the expression for the first filter condition:
    msxsl:string-compare(concat(substring(@MyDueDate, 1, 4), substring(@MyDueDate, 6, 2), substring(@MyDueDate, 9, 2)), concat(substring(../../my:startDate, 1, 4), substring(../../my:startDate, 6, 2), substring(../../my:startDate, 9, 2))) >= 0
  19. Click on the And » button behind the text box for the expression for the first filter condition to add a second filter condition.
  20. Select The expression in the drop-down list box for the second filter condition.
  21. Type in the following expression in the text box for the expression for the second filter condition:
    msxsl:string-compare(concat(substring(@MyDueDate, 1, 4), substring(@MyDueDate, 6, 2), substring(@MyDueDate, 9, 2)), concat(substring(../../my:endDate, 1, 4), substring(../../my:endDate, 6, 2), substring(../../my:endDate, 9, 2))) <= 0
  22. Click on the OK button when closing all open dialog boxes.
  23. Go to Tools > Form Options... and click on the Open and Save tab.
  24. Click on the Rules... button and then on the Add... button to add a rule with the following action:
    Query using a data connection: Main connection
  25. Click on the OK button when closing all open dialog boxes.
  26. Go to Tools > Submitting Forms....
  27. Select Custom submit using rules from the Submit to drop-down list box.
  28. Click on the Rules... button and then on the Add... button to add a rule with the following two actions in the order specified:
    Action 1:
    Submit using a data connection: Main connection
    Action 2:
    Query using a data connection: Main connection
  29. Click on the OK button when closing all open dialog boxes.

You should now have a fully functional InfoPath form that filters database records based on a date range using two date picker controls. You can add, edit, and delete records by using the functionality provided by the buttons on the repeating table, and then submit the form to write the changes back to the databse.

 
 Subscribe for updates via RSS or Email

Copyright: This article may not be used on web sites (whether personal or otherwise), copied, disseminated, altered, printed, published, broadcasted, or reproduced in any way without an expressed written consent of S.Y.M. Wong-A-Ton. The techniques demonstrated in this article may be used within any Microsoft InfoPath project. This article is provided without any warranties. Copyright for this article is non-transferrable and remains with the author, S.Y.M. Wong-A-Ton.

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners
InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013
InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners
InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010
InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers
InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services


Related InfoPath Articles:

ADVERTISEMENTS

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners

InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013

InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners

InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010

InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers

InfoPath 2010 Cookbook 4: 101 Code Recipes for VB Developers

InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services