Hi

This is a problem I don´t seem to get solved. The problem consists of that I would like to display a query in a form and in the same form I want multiple comboboxes which will work as criteria for a query. I should get it working by putting in following in the Criteria in the Query:
[forms]![SearchForm]![Country] OR ISNULL([forms]![SearchForm]![Country]) and for the other field:[forms]![SearchForm]![City] OR ISNULL([forms]![SearchForm]![City])

This should make it possible to search by Country, City or both - at the same time and the query should show all records over Country OR City OR Both. BUT - the thing is that I have to fill in both comboboxes before the query gives me any info.

I don´t get it to work.

Please help and I´m grateful for all the help I can get :-)

Take the OR ISNULL part of each field out. This is evaluating to True or False (most likly True) Thus you are telling it:

Where Country = True and City = "New Orleans"

I beleive it will ignore the blank value in the where

Hmmm....it still ditn´t work. The question remains, how is it possible to make a system where I can use several comboboxes as criterias for a query?

You can either leave the ISNull in the query or take it out (depending on what you want to search for and how your records look like). It won't solve your problem. When you change the value in your ComboBoxes, you need to refresh the data shown in your form according to the new settings.
I suggest you insert a code on AfterUpdate of all your ComboBoxes (form.recalc).
This might solve your problem.

Your concept is ok. You just have to put the conditions for each combobox on a separate line. In QBE view, Access connects each condition in the same row with the AND operator. Conditions on separate rows are connected with the OR operator.

So, on line 1 you would have:
[forms]![SearchForm]![Country] OR ISNULL([forms]![SearchForm]![Country]) under the proper table column
And, on line 2 you would have:
:[forms]![SearchForm]![City] OR ISNULL([forms]![SearchForm]![City]) under the other column

I have tried that but I still don´t get it to work. I still have to have all the criterias filles in the form and if I leave one out the query will open as empty.

I have no idea what do do... http://www.access-programmers.co.uk/ubb/smile.gif

how about if you make the wildcard character '*' available in the list for each combo box?

Then in your criteria use the 'Like' operator.
i.e. like([forms]![SearchForm]![Country]) and for the other field: like([forms]![SearchForm]![City]).

(also make '*' the default value for the combo boxes)

Admittedly this only works for text fields.

If your combo box control source is based on the table that you're querying against (i.e. you can't add the wildcard character to that table), it should be possible to do a union query in your combo box control source to add it on.

Mike

I'll email you a demo

Mike

Thanks Mike...the thing is that I can´t use the wildcard character since i have to use some search function using dates AND Text. As I meantioned...I have to be able to search for Sales by country, month and year. And its easy to do if all the criterias set but it´s abit harder if the user wan´t to leave one of the criterias empty in the textbox http://www.access-programmers.co.uk/ubb/smile.gif

OK, this works, but it's terribly messy:

1: Put a spare numeric field in the table you're querying (we'll call it 'score')

2: Run a quick update query to set them all to zero

3: Run a series of update queries against each individual criteria, add 1 to the score if the criteria matches.

4: Count up the number of combo boxes that are not null (=(not(isnull([forms]!SearchForm]![Country]))+ not isnull... )*-1

5: Use a select query for all records where the 'score' matches the count of non-null combo boxes.

6: Don't be surprised if anybody criticises this method.

Actually, you can also use this sort of thing to find, for example, records that match at least 4 -(but any 4)- of 8 criteria.

Alternatively I think it's possible to generate a bit of SQL dynamically, inserting only the relevant criteria...

...but I've never tried it!

Mike

I just looked at the example you sent me and it looks good. I´m sure I´ll find use for it....I´t would be possible to search for Year, month and day if they would have their own columns....on the other hand - then it´s harder to calculate normal date-functions instead. This really is hard...I mean can it be this hard to get the answer to a query as sales per Country/Total Sales and Month OR/AND Year without having to make a separate query for all different alternatives between countries(there are alot of them), year(about 10) and months (12 of course).

Well the last alternative is out of question... there has to be another way to do it...

Have a nice day and thank you Mike for all your help http://www.access-programmers.co.uk/ubb/smile.gif

I've mailed you another demo which works with date functions, I think this is what you're after.

I was thinking about introducing the possibility of greater than or less than operators, so you could select dates in a range, but that will have to wait for another day.

Mike

Thank you once again Mike. I had to check once again before I go home if you had answered. You are right about that that the best would be if it would be possible to search and have a result within a range. That would be very useful for me at this point. It would open a lot more possibilities for the database I´m making for my company.

Once again, I´m grateful for all your help - and have a nice day http://www.access-programmers.co.uk/ubb/smile.gif