Combos and Filters

ok I am going to try and explain this

I have a form (Form1) that has about 50 Option buttons.
These option buttons control combo boxes that are located on Form2. When a combo box is activated it needs to filter data in a query. This query is used to generate a report. Now if only one combo is active it is no problem since I can just use a filter for that situation (i.e. If combo1 is active then apply filter1, I put this in the onopen field of the report and the filter is done with a macro), but if multiple combo boxes are active and I need to apply multiple filters to the query then we have a problem. If I use the above idea then only the last If statement that is true will be applied how should I go about doing this?? I have tried creating a query for each combo, then filtering each individual query and doing a join, but when I put the if statement in the onopen part of the report it would give me an error that basically said you could only filter the reports underlying query which I have to set to the Joined query not the individual ones for the combos.


Well trying to explain this is very confusing, but any help would be much appreciated

I am pretty familiar with Access but have very little VBA experience

It sounds like you have a Form1, which spawns Form2, making sure only the correct ComboBoxes are enabled, and then Form2 runs a Report with QueryA as its Record Source. Am I understanding correctly?

David,

You have it correct. The problem is that I need each combo box selection on Form2 to eliminate some of the rows in QueryA.

I tried putting this in each of the criteria for Query2
[Forms]![Form2]![combo1] etc.
but if the combo was not made visible then it would still ask for the value for combo1 and the user will only want to enter the values for the combos that they activate

Thanks for the help and sorry this is kind of hard to explain



Darin

To make a long story short..

I would build the limitations (eliminating those rows) into a SQL "WHERE" clause that you build from Form2. An example would go something like this:
If it gives you trouble, put in a MsgBox strWhere right before the DoCmd line and see if you can spot an error in logic, etc. They can take some time to debug but you can't beat WHERE clauses for flexibility.

David,

I used the sub with only ORs and all was well. But I need to use it with the ANDs, and I am getting an error that InStrRev is not a valid function. I am using Access 97 on an nt machine. I am guessing that the Rev part is what access is complaining about.

I also want to make sure that my logic is correct on these ORs ANDs. Each combo box has multiple columns although all but one are hidden. So when I go to the query to eliminate records 2 things for each combo must be known (and they are both in the combo) thus the AND in my code. Each combo has nothing to do with the other one if we choose combo 1, 5, 7, 20. Then each one will need to eliminate some records based on their values thus the OR. I think that is what I am doing here with the current setup.

Here is what I have so far.


Private Sub formA2button_Click()
Dim strWhere As String

strWhere = "("

If Not IsNull(Me.type_combo) Then strWhere = strWhere & "[qry_RevOrd1]![BOTHVALUES] = '" & Me.type_combo.Column(2) & "' AND "
If Not IsNull(Me.type_combo) Then strWhere = strWhere & "[qry_RevOrd1]![NAME] = '" & Me.type_combo.Column(3) & "' OR "

If Not IsNull(Me.allow_combo) Then strWhere = strWhere & "[qry_RevOrd1]![BOTHVALUES] = '" & Me.allow__combo.Column(2) & "' AND "
If Not IsNull(Me.allow_combo) Then strWhere = strWhere & "[qry_RevOrd1]![NAME] = '" & Me.allow_combo.Column(3) & "' OR "

'etc etc. Make sure your ANDs and ORs make sense

If strWhere = "(" Then 'did we add anything to the string?
strWhere = "1 = 1" 'nope - include every record.
Else
'strWhere = left(strWhere, Len(strWhere) - 4) & ")"
'remove the last OR - if you mix ANDs and ORs you'll have to use this:
strWhere = left(strWhere, Len(strWhere) - InStrRev(strWhere, 3, " ")) & ")"

End If

MsgBox strWhere

DoCmd.OpenReport "Rev Ord Report", acViewPreview, , strWhere
'essentially - open the report, but only for the conditions laid out in strWhere
End Sub




Thanks for the help

I think you can simplify your logic a bit

Since if the combo is filled out for Column(2), it will be filled out for Column(3) as well, we only need to check it once. How about: And since your statements will always end in " OR ", if they're added, you can go back to using Left(Len()-6). In essence, you are cutting off the last six characters; the ") OR (" that wasn't needed.

I want to make sure I understand what you're doing correctly. You keep saying you are "eliminating rows" but you're actually specifying which rows are to be included. With your current ( X AND Y ) OR (Z AND A) ... setup, you are including rows where ANY of the combos are matched. Did you want only the ones where they are ALL matched? Use AND between the IFs as well; ") AND (", or leave off the extra parenthesis altogether since they won't be needed anymore.

David,

Yes you are corect I want the rows that match the criteria selected in all of the combos my terminology was a bit backwards (I meant eliminating the rows that dont match all of the combos). I am going to try and get this in today, but I got a 4 day weekend coming up so if not I'll let you know on Monday of next week.

Thanks Again
Darin

David

I am getting a runtime error 3075 that says I am missing a ) ] or item in my query expression. The syntax looks fine to me as far as the parens etc.

I think the problem may be due to the structure of my RevOrd1 query. RevOrd1 has several columns; Name, BOTHVALUES, A, B, C. Now when someone picks a value from a combo box it will correspond with one or many of the rows in RevOrd1 based on Name and BOTHVALUES that are chosen in the combo. If they choose another combo then it will correspond to another set of rows etc. I need to take these sets of rows and return only the ones where the As, Bs and Cs match in each. Currently it is giving me an error if I have more than one combo selected because as I understand I am trying to find/select a row where Name and BOTHVALUES = combo1 and combo2, but thats not possible because there is only one instance of Name and BOTHVALUES per row in the query.

This should have been explained better earlier on my part

I am beginning to think I am a little out of my league here

Thanks for all of the help
Darin

Can you post your queries?

If you go into your RevOrd1 query and go into SQL view, you'll get a text view you can copy and paste.

I'd also like to see an example of what the MsgBox strWhere statement gives right before you get the error message about missing closing parentheses or brackets.

The Message box reads as the following

(([qry_RevOrd1]![BOTHVALUES] = '=2' AND [qry_RevOrd1]![Name] = 'Type')
AND (([qry_RevOrd1]![BOTHVALUES] = '=Y' AND [qry_RevOrd1]![Name] =
'ALLOW-DK'))

These values are correct with the ones that were chosen in the combos so everything is working correctly as far as that goes




Here is the SQL for the query RevOrd1

SELECT COND.B, COND.A,
COND.C, COND.SYMBOL,COND.COMPARED_TO_VALUE, COND.NAME,
[SYMBOL] & " " & [COMPARED_TO_VALUE] AS BOTHVALUES
FROM COND;


I originally had select distinct, but they are distinct so it was not necessary


Darin

DKEND said:

The Message box reads as the following

(([qry_RevOrd1]![BOTHVALUES] = '=2' AND [qry_RevOrd1]![Name] = 'Type')
AND (([qry_RevOrd1]![BOTHVALUES] = '=Y' AND [qry_RevOrd1]![Name] =
'ALLOW-DK'))

Click to expand...

You've got 4 opening parentheses and only 3 closing parentheses. By the way your data is laid out I'd say you need to remove one of the ones after the main AND. "...= 'Type') AND (([qry_RevOrd1]!..."

Try reading your queries out loud to make sure they make sense. For instance in your case, with the correction listed above, it reads something like: "Records where 'BOTHVALUES' is '=2' and 'Name' is 'Type', and where 'BOTHVALUES' is '=Y' and 'Name' is 'ALLOW-DK'". Hopefully you see this isn't possible.

When I said to use AND between them, I meant to compare the different combos, not different values of the same combo. Try making the connector outside of the ( ) an OR again, if you're still working with the same fields.

I don't know if this will help or confuse, but here is an example of how I build a query string based on three different combos, any one of which can be true:

David,

I am trying the sub that you posted, but it may take awhile with my limited VBA experience. I do understand that the AND will not work, but the OR is not what I need. For example lets say that a person selected 2 combos X and Y if they make a selection for combo X then From query RevOrd1 they will know that the possible combinations of ABC are 123, 124, 126 and if they make a selection for combo Y then from RevOrd1 the possible combinations for ABC are 123, 125, 126. I need to return the values where both are true 123 and 126. The OR will give me all of the values where either is true (X or Y) 123-126. And since it is only one query RevOrd1 the AND will not work because of reasons we already discussed. I could make a query for each combo, but the query is extremely large and that would make things very inefficient. Its sort of like I need to store all of the ABC possibilities for each combo selection and then join all of them where ABC from combo1 = ABC from combo2 etc. I am determined to get this one and really appreciate you taking your time to help me on it.


Darin

Let me back up one step to make sure I get it:

ComboX has the values (among others) 123, 124, 126. Those are the ones that were selected on the popup form.
ComboY refers to the same field, but in this one they selected 123, 125, 126. Am I right? I don't understand why you have two combos for the same field, but maybe that's a failing of my vision.

I think I need to see the database. If you have the ability to compact it and place it in Acc2k format, you can email it to me at david_reynolds@kcmo.org - I'll take a look. Make sure you let me know what form I'm supposed to be using.

Combo X contains the values Name and BOTHVALUES.

The query RevOrd1 contains the values Name, BOTHVALUES, A, B, and C.

For exp
RevOrd1
Name BOTHVALUES A B C

Dog =3 1 2 3
Dog =5 1 3 4
Dog =5 1 6 8
Cat =4 1 3 4
Cat =5 3 4 4
Mouse =3 2 3 4
Car =7 1 2 3
House =4 3 7 9
House =4 5 8 5

The only thing that a person can see in the combo is BOTHVALUES
(in each combo the Name is already set, it is unique for each
combo but the same for all choices of BOTHVALUES in that combo).

When a user makes a selection (BOTHVALUES) from the combo,
it looks at the BOTHVALUES and the Name fields in RevOrd1.
This will give me a list of possible combinations of ABC.

Lets say Dog combo is activated then a user can select either =3 or =5 in the combo.
if they choose Dog=3 then we need to create a report that says
abc=123 If they choose Dog =5 then abc=134 and abc=168

Now say Dog and Cat are activated and for Dog the user selects =5
and for Cat the user selects =4 then we need to create a report that says
abc=134 if they would have said Cat =5 then we would return nothing

Hope that is a bit more clear although looking at it I'm not sure


I don't think I will be able to mail the db. Most of the tables are linked to db2 tables and I only have Acc97.

Thanks Again
Darin

It sounds to me like your real problem may be the number of possible filters you could apply, which leads to side effects of query string size and ever-increasing complexity.

The more combo boxes that have been used, the more complex the query becomes. The more complex the query becomes, the longer the equivalent SQL string becomes. And it won't take you long to bump into the string-size limits of Access. Particularly if you have 50 possible combo boxes to consider? If that number is realistic, then forget about a monolithic approach. 'tain't gonna happen, my friend.

I'd like to offer an alternative thought that might be disarmingly simple in its approach, but might also be easy even for someone who isn't very comfortable with VBA. It has a lot of simple steps and can be developed / tested incrementally.

NOTE: This next approach is a single-user-at-a-time approach. If this must be shared by multiple simultaneous users, it gets a lot trickier. If you really need this to be multi-user, I can offer some thoughts but no sure solutions.

In the table from which you are doing this selection/filtration, add one Yes/No field, call it "Selected." Its default value doesn't matter because you will never use its default value.

I think that perhaps you should not run any individual queries until all combo boxes have been ignored or visited as needed. Then make a command button that runs some VBA code for you. Let the Button Wizard help you get it started, then you can gut it and put in your own calls. (This is always good advice for folks new to Access - let the Wizard get you started, then go on to customize whatever gets conjured for you.)

By taking a "divide and conquer" approach, you might be able to do this with VBA and build up your confidence in that aspect of Access at the same time. The skeleton of what I'm suggesting is more or less like this...

Private Sub BigFilter_Click()

' first, reset the [Selected] flag so EVERYTHING is selected.

DoCmd.RunSQL "UPDATE [MyTable] SET [MyTable].[Selected]=True"

' now see if I can eliminate records using the first combo box

if Len(Nz([ComboBox1],"")) > 0 then

' box is occupied, build a query string. Filter out mismatches.

stMyQuery = "UPDATE [MyTable] SET [MyTable].[Selected]=False WHERE Criterion1 <> """ & Trim(ComboBox1) & """"

'NOTE: I just happened to use <> operator. You could also use =, >, <, or Between ... And ..., use whatever you need for whatever filtration rules you want to apply. Even a "like" operator if you remember to add the appropriate wildcards. You just have to know which box applies which rule.

' run the query (with Transaction mode set false)

DoCmd.RunSQL stMyQuery, False

' test whether the most recent update eliminated all records.

if DCount( "[Selected]", "MyTable", "[Selected]=True" ) = 0 then

' yes, all is gone. skip the rest of what would be a futile search.

msgbox "Query has eliminated all records."
Goto End_of_sequence

end if ' end of "If DCount(...) = 0"

end if ' end of "If ComboBox1 isn't empty"

' ... now do the same exact thing again for CBox2, CBox3, etc. until you have run out of combo boxes to use.

End_Of_Sequence:

End BigFilter_Click

This might be a little tedious, but on the other hand it could be keyed in once completely, followed by a bunch of cut-and-paste actions for subsequent filtration steps. It would surely do the trick for you. By doing the filtration one criterion at a time, it will take longer to run but will never run into the string-length barrier. Nor is it conceptually difficult.

Now, the payoff is that after the last combo box is checked, any records having [Selected] = True are the records you wanted to see, no matter HOW many combo-box queries you had to manage.

Obviously, if more than one person attempts to do this at the same time, you run into the problem of two sets of filters being done at once. If this happens to be a requirement, reply through this forum. But if you CAN get your users to agree to only do this one person at a time, you can keep it simple. Which is a good thing.

In the table from which you are doing this selection/filtration, add one Yes/No field, call it "Selected." Its default value doesn't matter because you will never use its default value.

Click to expand...

Doc_Man Thanks for helping. I do have one thing to ask. I am not actually filtering a table. I am doing the selection/filtration on a query. RevOrd1 (the qry) is ran on a db2 table. It contains all of the possible combinations of A,B,C,Name,BOTHVALUES and there are lots. can I do this same thing by adding a field to the query called selected. Or maybe even make some temp tables in Access to store the db2 info filter it and then delete it (of course I am not sure how that would work)

also where you are saying

stMyQuery = "UPDATE [MyTable] SET [MyTable].[Selected]=False WHERE Criterion1 <> """ & Trim(ComboBox1) & """"

Click to expand...

Would I use RevOrd1 (the query again) in place of MyTable

I do understand the concepts behind your approach

Thanks for the help guys and stay with me I will get this one eventually


Darin

I guess the field BOTHVALUES is what's confusing me

I'm visualizing a possible answer as being a query that links the 'values' selected in that concocted pair, and links the end results.

For example, instead of picking one combo that says '=Y', pick one that says '=' and another that says 'Y'. Now once you've got only the results which match those, only show the results they have in common.

Would that work as an alternate way to do what you're asking?

The combo actually has two other fields in it called Symbol and CompValue I set there width to 0. I joined these two fields in the combo so the user can see both it is not always =3 it could be >3 etc and called this BOTHVALUES. This allows the user to see the choices they made in the combo after they select it. If I had two columns in the combo only one would show up after you made a selection

So I also joined these two values (Symbol and Compvalue) in RevOrd1 and called it BOTHVALUES so that it would be easier to compare the choice made in the combo with the one in the query. Meaning you could do it with one comparison and not have to use two one for the symbol and one for the Compvalue.

As for that approach I am not real sure that I am clear on what you mean.

And what I'm trying to figure out is what that gains you..

Obviously it is the unique combination of '>' and '3' (let's say) that yields the result. But what makes it easier to do with them combined, that you couldn't do with two fields next to each other?

You say the first one can be '>' or '=' - are these mathematical expressions? Greater than 3, less than 3, equal to 3? I think I'm more confused than before.

Do we want to end up with a result that says "SELECT (records) WHERE [Symbol] = '>' AND [CompValue] = '3'"? Or is this just a first step to do something else to determine what the query actually should hold (is there a RevOrd1, RevOrd2, ....RevOrd50?)?

We started out with 50 combos. Now we have one, with two field values in it. Where did I fall off the wagon?

I have one RevOrd1. I am trying to stay clear of having multiple RevOrd1-57s. RevOrd1 has these values in it Name, Symbol, CompValue, Bothvalues(which is symbol and Compvalue combined), a,b,c.

I still have 50 combos (actually 57). Each combo has the following values. Name (which is unique to each combo and is known), Symbol, CompValue, BothValues. I have the following info for each combo..... bound column = 3 column widths =0;0;0;.5


I originally combined the Symbol and CompValue fields in each combo so that when a user activated the dropdown and made a choice they would be able to see the entire choice in the combo once the combo "lost focus". If I used the two columns (Symbol,CompValue) in the combo then after they made a choice all you would be able to see would be the bound column (i.e. the = or the 3 depending on which was bound).

Instead of having to compare 3 columns from the combo (symbol, CompValue, and Name) to RevOrd1 I just use the BOTHVALUES column along with Name from the combo and compare that to the Name and BOTHVALUES columns of RevOrd1 to get the possible ABCs

So really BOTHVALUES came about so that a user could see the choice they made in full and then I just used it for the comparison alsoe. I could use the two columns in the combo (symbol and CompValue) individually it really doesnt matter.

The fact that Symbol and CompValue are mathematical expressions is coincidence the values could be =A or <>A lots of different things maybe even 4= who knows just that they are two values with no real meaning. I just need to use them (or BOTHVALUES) coupled with the name to return a list of ABCs for each combo from RevOrd1.

Hope this clears the waters a bit. I'm still trying some of your guys ideas, but no dice so far.

And as always thanks again for all of the help


Darin