Error checking can alert the user that the value has been typed incorrectly, but it would be best to help the user along by prompting him or her with what the acceptable values are. In fact it would be ideal to give the user an enumerated list of options, so the user only has to choose the number of the desired value and enter one number instead of typing a long text string. The script below will do just that.
This script should be associated with the ActiveReports Document object and the OnReportStart event. That way it will pop up at run-time and provide a filter for the contents of the report.
After you paste the script into your VBScript editor window, be sure to update the value of the strColumnName variable so that it corresponds to the desired column in your actual query results. Also change the strPromptMessage and strPromptTitle variables so that the InputBox() prompt is worded appropriately for your report.
Sub OnReportStartSet oDict = CreateObject("Scripting.Dictionary")Set rsQuery = rpt.DataSource_Shelby_.RecordSetIf rpt.DataSource_Shelby_.RecordSet.RecordCount > 0 ThenintCounter = 1strCurrItem = ""strTestItem = ""strColumnName = "QueryColumnName"strPromptMessage = "Enter the prompt message here."strPromptTitle = "Enter the Prompt Title here"DostrCurrItem = rsQuery(strColumnName)If strCurrItem <> strTestItem ThenoDict.Add intCounter, strCurrItemstrList = strList & vbCR & intCounter & " - " & strCurrItemstrTestItem = strCurrItemintCounter = intCounter + 1End IfrsQuery.MoveNextLoop Until rsQuery.EOFDointChoice = InputBox(strPromptMessage & vbCr & strList, strPromptTitle)If intChoice <> "" and IsNumeric(intChoice) ThenstrItemChoice = oDict.Item(CInt(intChoice))rsQuery.Filter = strColumnName & " = '" & strItemChoice & "'"If rsQuery.RecordCount > 0 Then Exit Do Else rsQuery.Filter = ""End IfIf intChoice = "" Then Exit DoLooprsQuery.MoveFirstElseMsgBox("There are no results to use for this report. Please check the query and try again after you have a set of results.")End IfEnd Sub
With this code in your ActiveReports document, your end-user will have a much easier time filtering the report without have to double-check the spelling of a long text value and will avoid frustration of locating a tiny typo in the string.