Microsoft KB Archive/175830

= PRB: Using Wildcards in Filter of Data Form Wizard Created Form =

Article ID: 175830

Article Last Modified on 5/2/2006

-

APPLIES TO


 * Microsoft Visual InterDev 1.0 Standard Edition
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2

-



This article was previously published under Q175830



SYMPTOMS
The following error occurs when attempting to set filter criteria containing a wild card character at the beginning of the string:

Source: DataForm

Error Number: 20001

Description: Wildcard characters * and % can only be used at the end of the criteria



CAUSE
The DataForm Wizard produces code that uses the Recordset's Filter property. According to the ActiveX Data Objects (ADO) documentation on the Filter property (From Visual InterDev's InfoView select ADO Reference, ADO Objects, and then Recordset Object), "Only the asterisk (*) and percent sign (%) wild cards are allowed, and they must be the last character in the string."



RESOLUTION
In order to implement a solution that allows wild card characters at the beginning of filter criteria, you must make major modifications to the code produced by the DataForm Wizard.  Convert the DataForm Wizard generated code from a "Filter" model to a "Requery" model.

Make sure your code works correctly before moving on.  Search for "Filter = Session" and comment out the following line in xxxform.asp: 'rsxxx.Filter = Session("rsxxx_Filter")   Comment out the following lines in the "PrepFilterItem" function of xxxaction.asp: ' Check for leading wildcards 'If Left(strValue, 1) = "*" Or Left(strValue, 1) = "%" Then '  RaiseError errInvalidPrefix, strFieldName 'End If  Search for "Filter = Session" and comment out the following line in xxxlist.asp: 'rsxxx.Filter = Session("rsxxx_Filter")  If your filter uses multiple criteria -- like "field=0 and field2 like %xx%" -- you will have to make the following change in the last line of the "PrepFilterItem" function in xxxAction.asp:

From PrepFilterItem = "[" & strFieldName & "]" & " " & strOperator & " " & strDelimiter & strValue & strDelimiter To: PrepFilterItem = "(" & strFieldName & " " & strOperator & " " &     strDelimiter & strValue &  strDelimiter &")" 

<div class="status_section">

STATUS
This behavior is by design.

<div class="moreinformation_section">

Steps to Reproduce Behavior

 * 1) Enter %xxx% into one of the text boxes in the Filter page.
 * 2) Click Apply.

The error described in the "Symptoms" section occurs.

<div class="references_section">