newbie confused

[lbaccess101]

I have a query that works but I don't understand it. I have multiple criteria with "OR is null" written into each originally, then when I have and re-open, access creates new fields the query for those "Is Null" fields. Okay fine, but so then I get 10+ rows or criteria...and thus a very long sql statement. Smarter way of doing this or am I pushing the query limit? super lost....

[aytee111]

Start again from the beginning. Add one line of criteria, and one field at a time. Not sure why you are using "Like", that means that there are wild cards (*, ?, etc) in the criteria string, I can't see them so maybe they are at the end. If you remove it there won't be so much to look at. Then change the form fields to Nz(Forms!.....) then you won't have to worry about when the form field is empty.

[orange]

Also, it is usually helpful to readers -and to you -to describe what you are trying to do in plain, simple English so we get some context for the issue. In this case it is a question of using the query designer,and your use of "LIKE" may be the real issue.

Welcome and good luck.

[June7]

What you see is result of using OR operator and is normal.

As aytee111 notes, using LIKE operator is only meaningful if also using wildcard.

Why do you have IIf() in some of the parameters?

So if you do have the wildcard character concatenated in the parameters, try using AND operator instead of OR. Switch to SQL View to quickly edit the WHERE clause so there is only one set of the parameters with AND operator between them and then switch back to Design View.

I never use dynamic parameterized queries. I prefer to use VBA code to construct criteria. Review http://allenbrowne.com/ser-62.html

[lbaccess101]

That is all super helpful people thank you! <3 I must be doing something roundabout/unnecessary. I have several comboxes to narrow down criteria (from a form) and in a simpler query, the Like was working but perhaps I need better understanding of underlying query building....okay thanks! be back again I'm sure.

[lbaccess101]

When you say change form fields to Nz I'm not sure where you mean. In the actual form or in the query criteria build statement? I have a radio button where it's either option 1, option 2 or if I don't select either I want to view all records. ....

[lbaccess101]

What you see is result of using OR operator and is normal.

As aytee111 notes, using LIKE operator is only meaningful if also using wildcard.

Why do you have IIf() in some of the parameters?

So if you do have the wildcard character concatenated in the parameters, try using AND operator instead of OR. Switch to SQL View to quickly edit the WHERE clause so there is only one set of the parameters with AND operator between them and then switch back to Design View.

I never use dynamic parameterized queries. I prefer to use VBA code to construct criteria. Review http://allenbrowne.com/ser-62.html

I see Allene's 2 methods. His query is also has crazy long sql...so maybe I'm not that off. Okay I've been looking up the VBA method, seems it's more reliable for lengthy queries. Where do you start though? Do you build it as a sql statement after hitting "create new query/query design?" or do you create a button and in the backend of a form from which you want to run a query and write this all out somehow there in visual basic editor?...clearly I'm lost, I've never done it that way. Any pointers where to start? thanks again all!

[June7]

Incorporate whatever works for you into your db.

What exactly do you not understand about his examples? Did you download the database and explore?

[lbaccess101]

Incorporate whatever works for you into your db.

What exactly do you not understand about his examples? Did you download the database and explore?

Well yes, the one reffered to there called "search 2000" . He has a form and then a query called "alternative method" so, it's a little confusing what is going into the creating of that filter form. Do you need both actual query and the manual code in vba he wrote out when you click on cmdfilter in design view?

[June7]

The VBA approach does not use the 'alternative method' query, that's why it's called 'alternative method'.

The VBA code constructs a filter criteria string and applies that string to the Filter property of form. Or use it in the WHERE CONDITION argument of OpenForm or OpenReport commands.

If the 'alternative method' query is used then it would be the RecordSource of form being filtered and the VBA procedure building criteria string would not be used.