468,136 Members | 1,441 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,136 developers. It's quick & easy.

advanced filter

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


Nov 12 '05 #1
1 5340
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


Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Brad Shook | last post: by
reply views Thread by Andrew Meador - ASCPA, MCSE, MCP+I, Network+, A+ | last post: by
reply views Thread by Andrew Meador - ASCPA, MCSE, MCP+I, Network+, A+ | last post: by
27 posts views Thread by didacticone | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.