query by form issues HELP PLEASE!

[stuartkeith]

Hello there.

I hope you can help with the issuse that I am having with my criterea queries!


I can do the QBF, that bits not the issue, however its not behaving the way I thought it would! I have follwed what my book says and also http://support.microsoft.com/kb/304428/EN-US/ too, but the results are strange look at the images to see what I mean.

1) The user enters the required criteria into the form, leaving blank any boxes that are not required to be searched on.
The user then chooses weather they require the output as a report, or edit it into a form. FINE!

2 I am using the "forms!frm_searching!ctrName or forms!frm_searching!ctrName is null" in the query to grab the input from the above form.

This is what it looks like when I first create the query!

after I have entered the search into the form it may work or it may not, If I start to had more form input into the query either it returns no results, or it gives me the "search criteria is to complex to run" error.

Then back to the query design it looks like this!

What is going on?

 

[ablim]

Thats access letting you create a monster of query the shorthand way.

Because you have options (the OR bit) in your criteria access separates them out into seperate bits. I had one with 3 category levels and that was scary enough!

This is what i think it does, i could be wrong but here goes

The New coloums to the right of the query you created are basically checking what bits of the form are null. e.g.:

The top row of criteria caters for the situation were all the search terms are filled on your form so there are no null so the search results contain only the things that contain all the search terms on your form.

The second where all are filled apart from server type will contain the results where all the search terms filled are met no matter what the server type is.

You can see that the first of the new columns that says the server type feild on the form 'is null' and in the first column that relates to the table you are searching no critera is filled in giving you all server types in your results.

And so on for all the different scenarios for what is/is not filled in on your form