Filter Property

Sets or returns an expression used to filter the source recordset.

Syntax

dataSource.Filter[ = value As String ]

Remarks

Use the Filter property to restrict the records that you want to include in a report without modifying the RecordSource property.

For example, you may have created a Sales Report and would like to generate a report for a specific year. In this case, you could use the Filter property as shown below:

vsr.Load strReportFile, "Sales Report"

vsr.DataSource.Filter = "ShippedDate >= #1/1/1995# " & "AND " & _

"ShippedDate <= #1/1/1996#"

vsr.Render vp

The Filter string is made up of clauses in the form FieldName-Operator-Value (for example, "LastName = 'Smith'"). You can create compound clauses by concatenating individual clauses with AND (for example, "LastName = 'Smith' AND FirstName = 'John'") or OR (for example, "LastName = 'Smith' OR LastName = 'Jones'"). Use the following guidelines for criteria strings:

1. FieldName must be a valid field name from the Recordset. If the field name contains spaces, you must enclose the name in square brackets.

2. Operator must be one of the following: <, >, <=, >=, <>, =, or LIKE.

3. Value is the value with which you will compare the field values (for example, 'Smith', #8/24/95#, 12.345, or $50.00). Use single quotes with strings and pound signs (#) with dates. For numbers, you can use decimal points, dollar signs, and scientific notation. If Operator is LIKE, Value can use wildcards. Only the asterisk (*) and percent sign (%) wild cards are allowed, and they must be the last character in the string. Value cannot be null.

NOTE: To include single quotation marks (') in the filter Value, use two single quotation marks to represent one. For example, to filter on O'Malley, the criteria string should be "col1 = 'O''Malley'". To include single quotation marks at both the beginning and the end of the filter value, enclose the string with pound signs (#). For example, to filter on '1', the criteria string should be "col1 = #'1'#".

If you want to allow users to select parameters for the report, consider using Parameter Queries instead of filters. A parameter query is a query that when run displays its own dialog box prompting the user for criteria for retrieving records. For details, see the RecordSource property.

Data Type

String