One way is to have a completely dynamic where clause, e.g.,
CREATE SQL VIEW lvMyView AS ;
SELECT * FROM parcel ;
WHERE &?lcPermitSearch
Notes:
The "&" goes in front of the "?"
This cannot be done in the view designer.
What you can do with the View Designer, however, is add the fields as you would want them, set the updatability of each, etc., then run GenDBC[X] -- actually GetView() from GenDBC[X] -- to get the definition of the view.
Then you can add the
WHERE &?lcPermitSearch
in the code and recreate the view.
Then all you would have to is USE or REQUERY() the view instead of the SELECT.
>I have a form that has several controls used to allow users to enter in search criteria. I build a WHERE clause based on what is entered by the user. Example below:
>
>lcPermitSearch='' &&Holds final WHERE clause
>
>**parcel search
>IF !EMPTY(THISFORM.txtparcel.value)
> lcwork = "parcel='"+ALLTRIM(THISFORM.txtparcel.value)+"'"
> lcPermitSearch = IIF( EMPTY(lcPermitSearch), ;
> ' WHERE '+lcwork, ;
> lcPermitSearch + ' AND '+lcwork)
>ENDIF
>**Address search
>IF !EMPTY(THISFORM.txtaddress.value)
> lcwork = "situs_addr='"+ALLTRIM(THISFORM.txtaddress.value)+"'"
> lcPermitSearch = IIF( EMPTY(lcPermitSearch), ;
> ' WHERE '+lcwork, ;
> lcPermitSearch + ' AND '+lcwork)
>ENDIF
>
>lcPermitSearch = lcPermitSearch +' ' &&This is my final WHERE clause
>
>**********************************************************************
>
Insanity: Doing the same thing over and over and expecting different results.