Offline Publication Filtering Reference

Offline publication filtering provides a mechanism for limiting the information contained in a publication. When creating your offline publications, you can use filters to make specialized publications by specifying a simple expression.

For more information about creating offline publications, see Allowing Users to Work Offline.

The Microsoft Access Workflow Designer for Microsoft SQL Server™ helps you create these filter expressions through a simple interface where you identify a column from the table, an operator, and a value by which to filter. When you click Add, the Access Workflow Designer creates the T-SQL expression and inserts it at the current insertion point. If the current insertion point is at the beginning of the text field, no AND/OR keyword is produced.

You also can edit the expression in the text field directly. When you click OK, the Access Workflow Designer validates the SQL text. If the SQL text is not valid, a message appears warning you of problems.

The operators available depend on the data type of the selected columns, and the values available depend on the operator selected. Some operators have predefined values, but the Values field is a combo box that makes it possible for users to either select from the list or type in their own values.

Filtering Operators Available for the Different Data Types

Character (char, nchar, ntext, nvharchar, varchar)

Operators available	Values	SQL statement
Equals		`[table].[column] = "value"`
Is Not Equal to		`[table].[column] <> "value"`
Is Like		`[table]. [column] LIKE "value"`
Is Not Like		`[table]. [column] NOT LIKE "value"`
Is User	"Current user," account names from the modUserList view	`[table]. [column] = SUSER_SNAME()` `[table]. [column] = "value"`
Is Not User	"Current user," account names from the modUserList view	`[table]. [column] <> SUSER_SNAME()` `[table]. [column] <> "value"`
Is Null		`[table]. [column] IS NULL`
Is Not Null		`[table]. [column] IS NOT NULL`

Text/binary (binary, image, ntext, text, varbinary, uniqueidentifier)

Operators available	SQL statement
Is Like	`[table]. [column] LIKE "value"`
Is Not Like	`[table]. [column] NOT LIKE "value"`
Is Null	`[table]. [column] IS NULL`
Is Not Null	`[table]. [column] IS NOT NULL`

Number (int, smallint, numeric, decimal, float, real, tinyint, money, smallmoney)

Operators available	SQL statement
Less Than	`[table]. [column] < value`
Less Than or Equal to	`[table]. [column] <= value`
Greater Than	`[table]. [column] > value`
Greater Than or Equal to	`[table]. [column] >= value`
Equals	`[table]. [column] = value`
Not Equal to	`[table]. [column] <> value`
Is Null	`[table]. [column] IS NULL`
Is Not Null	`[table]. [column] IS NOT NULL`

Int with foreign key to lookup table (DL in modObjects)

Operators available	Values	SQL statement
Less Than	List of lookup keywords	`[table]. [column] < value`
Less Than or Equal to	List of lookup keywords	`[table]. [column] <= value`
Greater Than	List of lookup keywords	`[table]. [column] > value`
Greater Than or Equal to	List of lookup keywords	`[table]. [column] >= value`
Equals	List of lookup keywords	`[table]. [column] = value`
Not Equal to	List of lookup keywords	`[table]. [column] <> value`
Is Null		`[table]. [column] IS NULL`
Is Not Null		`[table]. [column] IS NOT NULL`

Dates (datetime, smalldatetime, timestamp)

Operators available	Values	SQL statement
Equals	"Today", "1 day ago", "1 day from now", (repeat for 2-6 days), "1 week ago", "1 week from now", (repeat for 2-4 weeks), "1 month ago", "1 month from now", (repeat for 2,4,6 months), "1 year from now", "1 year ago"	`DATEDIFF(day, [table]. [column], CURRENT_TIMESTAMP) = 0` `DATEDIFF(day, [table]. [column], CURRENT_TIMESTAMP) = n DATEDIFF(day, [table]. [column], CURRENT_TIMESTAMP) = -n DATEDIFF(week, [table]. [column], CURRENT_TIMESTAMP) = n DATEDIFF(week, [table]. [column], CURRENT_TIMESTAMP) = -n DATEDIFF(month, [table]. [column], CURRENT_TIMESTAMP) = n DATEDIFF(month, [table]. [column], CURRENT_TIMESTAMP) = -n DATEDIFF(year, [table]. [column], CURRENT_TIMESTAMP) = -1 DATEDIFF(year, [table]. [column], CURRENT_TIMESTAMP) = 1`
Not Equal to	"Today", "1 day ago", "1 day from now", (repeat for 2-6 days), "1 week ago", "1 week from now", (repeat for 2-4 weeks), "1 month ago", "1 month from now", (repeat for 2,4,6 months), "1 year from now", "1 year ago",	`DATEDIFF(day, [table]. [column], CURRENT_TIMESTAMP) <> 0` `DATEDIFF(day, [table]. [column], CURRENT_TIMESTAMP) <> n DATEDIFF(day, [table]. [column], CURRENT_TIMESTAMP) <> -n DATEDIFF(week, [table]. [column], CURRENT_TIMESTAMP) <> n DATEDIFF(week, [table]. [column], CURRENT_TIMESTAMP) <> -n DATEDIFF(month, [table]. [column], CURRENT_TIMESTAMP) <> n DATEDIFF(month, [table]. [column], CURRENT_TIMESTAMP) <> -n DATEDIFF(year, [table]. [column], CURRENT_TIMESTAMP) <> -1 DATEDIFF(year, [table]. [column], CURRENT_TIMESTAMP) <> 1`
Before	"Today", "1 day ago", "1 day from now", (repeat for 2-6 days), "1 week ago", "1 week from now", (repeat for 2-4 weeks), "1 month ago", "1 month from now", (repeat for 2,4,6 months), "1 year from now", "1 year ago"	`DATEDIFF(day, [table]. [column], CURRENT_TIMESTAMP) > 0` `DATEDIFF(day, [table]. [column], CURRENT_TIMESTAMP) > n DATEDIFF(day, [table]. [column], CURRENT_TIMESTAMP) > -n DATEDIFF(week, [table]. [column], CURRENT_TIMESTAMP) > n DATEDIFF(week, [table]. [column], CURRENT_TIMESTAMP) > -n DATEDIFF(month, [table]. [column], CURRENT_TIMESTAMP) > n DATEDIFF(month, [table]. [column], CURRENT_TIMESTAMP) > -n DATEDIFF(year, [table]. [column], CURRENT_TIMESTAMP) > -1 DATEDIFF(year, [table]. [column], CURRENT_TIMESTAMP) > 1`
Before or Equal to	"Today", "1 day ago", "1 day from now", (repeat for 2-6 days), "1 week ago", "1 week from now", (repeat for 2-4 weeks), "1 month ago", "1 month from now", (repeat for 2,4,6 months), "1 year from now", "1 year ago"	`DATEDIFF(day, [table]. [column], CURRENT_TIMESTAMP) >= 0` `DATEDIFF(day, [table]. [column], CURRENT_TIMESTAMP) >= n DATEDIFF(day, [table]. [column], CURRENT_TIMESTAMP) >= -n DATEDIFF(week, [table]. [column], CURRENT_TIMESTAMP) >= n DATEDIFF(week, [table]. [column], CURRENT_TIMESTAMP) >= -n DATEDIFF(month, [table]. [column], CURRENT_TIMESTAMP) >= n DATEDIFF(month, [table]. [column], CURRENT_TIMESTAMP) >= -n DATEDIFF(year, [table]. [column], CURRENT_TIMESTAMP) >= -1 DATEDIFF(year, [table]. [column], CURRENT_TIMESTAMP) >= 1`
After	"Today", "1 day ago", "1 day from now", (repeat for 2-6 days), "1 week ago", "1 week from now", (repeat for 2-4 weeks), "1 month ago", "1 month from now", (repeat for 2,4,6 months), "1 year from now", "1 year ago"	`DATEDIFF(day, [table]. [column], CURRENT_TIMESTAMP) < 0` `DATEDIFF(day, [table]. [column], CURRENT_TIMESTAMP) < n DATEDIFF(day, [table]. [column], CURRENT_TIMESTAMP) < -n DATEDIFF(week, [table]. [column], CURRENT_TIMESTAMP) < n DATEDIFF(week, [table]. [column], CURRENT_TIMESTAMP) < -n DATEDIFF(month, [table]. [column], CURRENT_TIMESTAMP) < n DATEDIFF(month, [table]. [column], CURRENT_TIMESTAMP) < -n DATEDIFF(year, [table]. [column], CURRENT_TIMESTAMP) < -1 DATEDIFF(year, [table]. [column], CURRENT_TIMESTAMP) < 1`
After or Equal to	"Today", "1 day ago", "1 day from now", (repeat for 2-6 days), "1 week ago", "1 week from now", (repeat for 2-4 weeks), "1 month ago", "1 month from now", (repeat for 2,4,6 months), "1 year from now", "1 year ago"	`DATEDIFF(day, [table]. [column], CURRENT_TIMESTAMP) <= 0` `DATEDIFF(day, [table]. [column], CURRENT_TIMESTAMP) <= n DATEDIFF(day, [table]. [column], CURRENT_TIMESTAMP) <= -n DATEDIFF(week, [table]. [column], CURRENT_TIMESTAMP) <= n DATEDIFF(week, [table]. [column], CURRENT_TIMESTAMP) <= -n DATEDIFF(month, [table]. [column], CURRENT_TIMESTAMP) <= n DATEDIFF(month, [table]. [column], CURRENT_TIMESTAMP) <= -n DATEDIFF(year, [table]. [column], CURRENT_TIMESTAMP) <= -1 DATEDIFF(year, [table]. [column], CURRENT_TIMESTAMP) <= 1`
Is Null		`[table]. [column] IS NULL`
Is Not Null		`[table]. [column] IS NOT NULL`

Bit

Operators available	Values	SQL statement
Equals	"True", "False"	`[table]. [column] = 1` `[table]. [column] = 0`
Not Equal	"True", "False"	`[table]. [column] <> 1` `[table]. [column] <> 0`
Is Null		`[table]. [column] IS NULL`
Is Not Null		`[table]. [column] IS NOT NULL`

Note The lookup keywords should be listed as "value - keyword," where value is the integer value stored in the table.