Select Query, from Form fields that are not null

[Diogo]

OK

Need help with the following problem:

I want to create a form for the user to fill the search criteria, that will
be used in a query.
The form would have 6 fields and I would like to write code that would see
which fields the user wrote and use those known fields has search criteria in
a query.

Something like:

Field1 = asdf
Field2 = ....
Field3 = asdf
Field4 = ....
Field5 = ....
Field6 = ....

If Field1 not null and ...

Then select * from table1 where Field1=[Form1.Field1.Value] and ...

Where the rest would be the non null fileds in the form that the user uses?

 

[Paolo]

Hi Diogo,
I would do in this way (aircode)

str_sql=""
if field1="" and isnull(field1) then
str_sql="field1=""" & field1 & """ and"
endif

if field2="" and isnull(field2) then
if str_sql="" then
str_sql="field2=""" & field2 & """ and"
else
str_sql= str_sql & " field2=""" & field2 & """ and"
endif
endif

etc. etc. for every field. When you checked all fields

if str_sql<>"" then
str_sql=left(str_sql,len(str_sql)-4) ' so you remove the extra and
endif

yourquery="select * from table1 where " & str_sql

In the example I wrote I quoted the fields assuming that are text fields.
For the fields that are numeric, no need for quotes. If you have dates fields
use the hash(#)

HTH Paolo