>Here's the situation:
>
>The form have a 3 pages page frame:
>Page 1: edit fields and navigations buttons
>Page 2: list of all available records in a grid
>Page 3: filtering options
>
>They are many different combination of filtering conditions that the user can make.
>
>On what should I base my form: a view or a SELECT-SQL?
>
>A SELECT-SQL seem easier to use, but I can't edit it directly. A view would be more appropriate to do the editing, but the filtering options are bothering me. How can you build a view with changing criteria? Build a view programmaticaly? If so, would I need to initialize every fields "RecordSource" property everytime the filter is changed?
>
>I need your advise on this.
>
>TIA
Sylvain, here's my $.02US.
I use a SQL statement, with only the clauses needed by the user included. Myron's suggestion about using a macro-expansion for a WHERE clause is an excellent one. Lets assume that on your filtering page you have 5 criteria, and the user can choose any or all. You'd build the SQL-WHERE clause from the non-empty criteria:
cWhere = ''
if !empty(QBE1)
cWhere = cWhere + 'field1 = "' + QBE1 + '" AND '
endif
if !empty(QBE2)
cWhere = cWhere + 'field2 = "' + QBE2 + '" AND '
endif
.....
if len(cWhere) > 5
cWhere = substr(cWhere, 1, len(cWhere)-5)
endif
SELECT ..... WHERE &cWhere ......
Note a couple of items: Included in each portion of the cWhere variable are quote marks surrounding the value of QBE1, QBE2, etc. This is because you are using the VALUE of these variables in your clause, not the VARIABLE.
Also, you need to allow for the situation where there are NO items chosen by the user.
HTH