advanced filter

[Johan]

I have made a form (filterform) where I can select 3 conditions, to open
another form (showrecords), filtered with the 3 conditions.

On the filterform: 3 drop down boxes, where the user kan choose: name,
machine, area. And a button "start search".
By clicking the button, showrecords opens with filter: name = x and machine
= y and area = z.

That works good!

But what I don't find is to make it possible for the user to, by example,
only give a value to 1 or 2 of the criteria on filterform and opens
showrecords only with those criteria. When I let a drop box empty, the
filter looks for empty records. When I use a wildcard, it also don't works.

Anybody knows a solution?

Johan

[Arno R]

Johan,
2 options: (Second option is best)

1: In your query use conditions as Like Forms!FilterFormName!Name & "*"
This works in most cases but gives unwanted results based on the possible values of 'Name'
e.g. Your combo says "John" and in results also "Jonathan" is shown.

2: Construct a whereClause (strWhere) in runtime based on the combos with chosen values
Aircode: Me points to your form, also assuming name, machine and area are textfields (mind the
quotes)

Dim strWhere as string
strWhere = "1=1" 'This is *always* true ;-)
if not isnull(Me!Name) then strWhere =strWhere & " AND Name = '" & Me!Name & "'"
if not isnull(Me!Machine) then strWhere =strWhere & " AND Machine = '" & Me!Machine & "'"
if not isnull(Me!Area) then strWhere =strWhere & " AND Area = '" & Me!Area & "'"
Then open your form with Docmd.Openform "FormName", , , strWhere

--
Hope this helps
Arno R
"Johan" <jo***********@pandora.be> schreef in bericht
news:Sj*******************@phobos.telenet-ops.be...

I have made a form (filterform) where I can select 3 conditions, to open
another form (showrecords), filtered with the 3 conditions.

On the filterform: 3 drop down boxes, where the user kan choose: name,
machine, area. And a button "start search".
By clicking the button, showrecords opens with filter: name = x and machine
= y and area = z.

That works good!

But what I don't find is to make it possible for the user to, by example,
only give a value to 1 or 2 of the criteria on filterform and opens
showrecords only with those criteria. When I let a drop box empty, the
filter looks for empty records. When I use a wildcard, it also don't works.

Anybody knows a solution?

Johan