Microsoft KB Archive/156169

= How To Ignore Parameters in View or Query If Blank =

Article ID: 156169

Article Last Modified on 6/30/2004

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 6.0 Professional Edition

-



This article was previously published under Q156169



SUMMARY
This article describes two examples on how to return records that meet a specific criteria and how to ignore the criteria if a parameter is empty or null.



Example 1: Parameter View with Values Passed from Controls on a Form
  Open the Tastrade project. The project is in one of the following locations:   Visual FoxPro 3.0: Samples\Mainsamp Visual FoxPro 5.0: Samples\Tastrade Visual FoxPro 6.0: _samples variable   Create a new program, click the Code tab, and add the following lines of code: IF "6.00" $ VERSION cdir = _samples + "Tastrade" ELSE IF "5.00" $ VERSION cDir = HOME+"SAMPLES\TASTRADE" ELSE cDir = HOME+"SAMPLES\MAINSAMP" ENDIF ENDIF OPEN DATABASE cDir+"\DATA\TASTRADE.DBC" EXCLUSIVE CREATE SQL VIEW MyView as SELECT *; FROM tastrade!Employee; WHERE ((ALLTRIM(Employee.employee_id)==?vEmpid);        OR (EMPTY(?vEmpid)=.T.)); AND ((Employee.title == ?vTitle; OR (EMPTY(?vTitle)=.T.))) Save it as MyViewCode.prg and then Run the program.  Create a new form and save it as frmMyParam. Add MyView to the DataEnviroment object of the frmMyParam form. View the properties of cursor1 and set the NoDataOnLoad property to .T. - True.  Add the following controls to the form and set the properties as indicated:      Text Box Name: Text1

Text Box Name: Text2

Grid Name: Grid1 RecordSource: MyView

Command Button Name: Command1 Caption: Requery

Command Button Name: Command2 Caption: Clear   Add the following code to the Click event of the Command1 button: vEmpid=Thisform.Text1.Value vTitle=Thisform.Text2.Value =REQUERY('MyView') ThisForm.Refresh </li>  Add the following code to the Click event of the Command2 button: Thisform.Text1.Value="" Thisform.Text2.Value="" Thisform.Refresh </li>  Save and Run the form with following combination of values in the table below, and then click the Command1(Requery) button. Before you test the next combination of values, click the Command2(Clear) button. <pre class="fixed_text">  Text1      Text2       Results

All records are return

1             One record with Employee ID is 1

1      Sales Manager  One record with Employee Id is 1 and Title is Sales Manager

Sales Manager Two records return with Title being Sales Manager

2      Sales Manager  No records are returned </li></ol>

Example 2: Parameter Query
<ol> Open the project called Tastrade.</li>  Create a new program under the Code tab with the following lines of code, save it as MyQueryCode and then Run the program: CLOSE DATABASE IF "6.00" $ VERSION cdir = _samples ELSE IF "5.00" $ VERSION cDir = HOME+"SAMPLES\TASTRADE" ELSE cDir = HOME+"SAMPLES\MAINSAMP" ENDIF USE cDir+"\DATA\employee.dbf" CLEAR ACCEPT "Enter an Employee ID: " to vEmpid ACCEPT "Enter an Employee Last Name: " to vTitle SELECT *; FROM tastrade!Employee; WHERE ((ALLTRIM(Employee.employee_id)==?vEmpid);        OR (EMPTY(?vEmpid)=.T.)); AND ((Employee.title == ?vTitle; OR (EMPTY(?vTitle)=.T.))) </li> Use the same data as stated in the previous example for a test. For (in the table) press Enter at the prompt and the results returned should be the same as in Example 1.</li></ol>

Keywords: kbhowto kbsqlprog KB156169

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.