Query By form - SQL? Help (1 Viewer)

[allcarfan]

I created a form with several combo boxes on it. When data is chosen within the combo boxes, a query/report can be run and it retrieves all of the information in accordance with the combo boxes.

The functionality that I need to build in is the option to leave any number of those combo boxes blank in order to retrieve ALL information from the field linked to that combo.

Example:

I have three combo boxes. One is named team and it lists all team names. The second combo box is priority (1-2-3-4 etc). The Last combo box is day of the week. As it stands now, I can input data into each of these boxes and my report functions fine. However, I want to be able to leave one or more of them blank and it pull back info. Such as....if I dont choose a specific team, but I choose a specific priority and a specific day of the week, it should pull back ALL teams that have that priority and day of the week.

Does this make sense?

I was given a link to a site by the name of Brinkster to include an ALL function on my combo box, but that will not work for some reason. I know what I want to do is possible, because I have incorporated it into another database of mine. However, I cant make heads or tails of my notes or how that database is setup.

Help?

need more info?

 

[FoFa]

The easiest way is to default your combobox's to astrik (*) in the bound column and default your query criteria from = to LIKE. That way your query will use the wildcard when no selection is made in your combobox/s.

 

[allcarfan]

The easiest way is to default your combobox's to astrik (*) in the bound column and default your query criteria from = to LIKE. That way your query will use the wildcard when no selection is made in your combobox/s.

Im pretty new at this...where can I change = to LIKE?

 

[FoFa]

In your query. If you use the query form, in the criteria where you normally put the (equal to) value which I assume right now is something of the nature FORMS!MyForm!MyCombobox , just code LIKE FORMS!MyForm!MyCombobox and that should handle it.

 

[allcarfan]

Reviving this thread.

I attempted to use the ALL feature that is listed above. Unfortunately, when the ALL function was used with multiple criteria, it would only return the first ALL critieria and would not filter any other criteria selected within the other textboxes.

Let me explain further what I am trying to do:

The problem:

I have a form with 5 Combo Boxes on it. Each combo box pulls values from a seperate table. Example: Combo Box: Team lists the all of the teams listed in the TeamTable.

Based on what combination of criteria is selected on this for within the combo boxes is what information is returned on a report. Example: I select Team: RED...and then in the next combo box I select Number of Hours worked: 7 - when I click submit a query is run to pull back all information that matches team red when they worked a total of 7 hours. The query runs and automatically creates a report that lists all of the occurences of Team Red, 7 hours.

That all works. I can choose any combination of data and it pulls back that data. Here is where I am having the problem:

I want to be able to leave any number of the combo boxes blank and it pull back ALL data for that particular combo box. Example: i run another query. I leave the Team combo box blank...rather than selecting red. However, I still choose number of hours...7. I run the query....It should pull back ALL teams that worked a total of 7 hours.....Team RED, BLUE, GREEN....
I need it to work in reverse too. If I leave the number of hours blank and select Team RED, it needs to pull back ALL info for team red, regardless of the hours that worked. I need this functionality for each Combobox. I tried using a select ALL feature, but I could not get it to work correctly. It only worked with one textbox, it wouldnt recognize any other criteria selected in another combo box.
If I leave ALL comboboxes blank, it should pull back ALL data within the database.

I know this functionality is possible because I have designed another database with the same tools. I just cant figured out how I did it. I know that the key is entering in the correct expressions and "Is Null" values within my query.

So, can anyone shed any light or know someone that could help? Someone that knows what they are doing could have that done in no more than 5 minutes. I can even email the database if someone needs to take a look at it.

 

[Johnvandamme]

put it in zip format on the forum. I think this is a relation problem..
can happen in dbase world

 

[allcarfan]

John,

I appreciate your response. FORTUNATELY.....I got it to work. I have been trying for 3 days to figure out why this wasnt working. I come into the office this morning and 30 minutes later...viola.

Thanks everyone.