complex filtering through form?

[dystopias]

i've got a table, [datalist] that contains four fields called, [witness_1],[witness_2],[witness_3],[witness_4].

I've also got a table called [witnesses] that only has one field called [witness] which is simply a list of all witnesses.

I've got a form called [frmrunreports] that currently has four combo boxes called, [witness_1],[witness_2],[witness_3],[witness_4] with each row source being the [witness] field in the [witnesses] table.

Here's the goal: I want to be able to select 1, 2, 3, or 4 witness on the form and generate the report to only display entries with that combination of witnesses. problem is, the witnesses won't necessarily be in the same order as the form, I.E. [datalist]![witness_1] won't neccessarily match up with [frmrunreports]![witness_1]. how do i accomplish this kind of filtering when the order doesn't matter - the records should come if one record has "john" in the witness_1 field and "sam" in the witness_2 field and another record has "sam" in the witness_1 field and "john" in the witness_2 field.

I'm running xp with access 2003. i don't kow basic (i've been teaching myself access) so go easy on me.

Thanks!

-Mitchell

 

[heedaf]

If you want to do complicated tasks you really need to learn Visual Basic. Outside of code you could try using an additional field or table to set the order. More detail on what you are trying to do would help.
DeWayne

 

[dystopias]

More detail on what you are trying to do would help.
DeWayne

alright, say i want a report that will only disply records that have john, sam, bill, and rachel as witnesses. here are a couple example records:

[witness_1]-------[witness_2]-------[witness_3]-----[witness_4]---[date]
john -------------sam -------------rachel ---------- bill ------1/25/08
sam -------------john ------------- bill -------------rachel------1/20/08
john ------------- bill ------------- rachel--------------------- 12/20/07

so, on the form, i've got four combo boxes that get their rows from a table that is just a list of witnesses. say I choose:

combo 1 ------------combo 2 ------------combo3------------combo4
john---------------rachel --------------sam ----------------bill

i want that to then generate a report that would give me records 1 and 2 above, but not 3. i'm capable of cutting and pasting code, but i'm new to it.

 

[ajetrumpet]

[witness_1]-------[witness_2]-------[witness_3]-----[witness_4]---[date]
john -------------sam -------------rachel ---------- bill ------1/25/08
sam -------------john ------------- bill -------------rachel------1/20/08
john ------------- bill ------------- rachel--------------------- 12/20/07

say I choose:

combo 1 ------------combo 2 ------------combo3------------combo4
john---------------rachel --------------sam ----------------bill

i want that to then generate a report that would give me records 1 and 2 above, but not 3.

I don't think you're wanting things to get complicated, but regardless, here is one answer to the question that you actually asked:

Base the report off of a query, and write this in the SQL window of it:

SELECT * FROM [yourTable] WHERE

([witness_1] = forms!formname!combo1 OR 
   [witness_1] = forms!formname!combo2 OR
      [witness_1] = forms!formname!combo3 OR
         [witness_1] = forms!formname!combo4) AND

([witness_2] = forms!formname!combo1 OR 
   [witness_2] = forms!formname!combo2 OR
      [witness_2] = forms!formname!combo3 OR
         [witness_2] = forms!formname!combo4) AND

([witness_3] = forms!formname!combo1 OR 
   [witness_3] = forms!formname!combo2 OR
      [witness_3] = forms!formname!combo3 OR
         [witness_3] = forms!formname!combo4) AND

([witness_4] = forms!formname!combo1 OR 
   [witness_4] = forms!formname!combo2 OR
      [witness_4] = forms!formname!combo3 OR
         [witness_4] = forms!formname!combo4);

This might also be of interest to you:

http://www.access-programmers.co.uk/forums/showthread.php?t=103312

 

[dystopias]

thanks for you response adam. I tried your code and it didn't return any results. Then i read the link you suggested and added some stuff and tried this code:

SELECT * FROM [yourTable] WHERE

([witness_1] = forms!frmrunreports!witness_1 OR
[witness_1] = forms!frmrunreports!witness_2 Or [forms]![frmrunreports]![witness_2] is null OR
[witness_1] = forms!frmrunreports!witness_3 Or [forms]![frmrunreports]![witness_3] is null OR
[witness_1] = forms!frmrunreports!witness_4 Or [forms]![frmrunreports]![witness_4] is null) AND

([witness_2] = forms!frmrunreports!witness_1 OR
[witness_2] = forms!frmrunreports!witness_2 Or [forms]![frmrunreports]![witness_2] is null OR
[witness_2] = forms!frmrunreports!witness_3 Or [forms]![frmrunreports]![witness_3] is null OR
[witness_2] = forms!frmrunreports!witness_4 Or [forms]![frmrunreports]![witness_4] is null) AND

([witness_3] = forms!frmrunreports!witness_1 OR
[witness_3] = forms!frmrunreports!witness_2 Or [forms]![frmrunreports]![witness_2] is null OR
[witness_3] = forms!frmrunreports!witness_3 Or [forms]![frmrunreports]![witness_3] is null OR
[witness_3] = forms!frmrunreports!witness_4 Or [forms]![frmrunreports]![witness_4] is null) AND

([witness_4] = forms!frmrunreports!witness_1 OR
[witness_4] = forms!frmrunreports!witness_2 Or [forms]![frmrunreports]![witness_2] is null OR
[witness_4] = forms!frmrunreports!witness_3 Or [forms]![frmrunreports]![witness_3] is null OR
[witness_4] = forms!frmrunreports!witness_4 Or [forms]![frmrunreports]![witness_4] is null);
and this time got everything!

sometimes there might only be one witness selected, and sometimes all four.

 

[ajetrumpet]

thanks for you response adam. I tried your code and it didn't return any results.

sometimes there might only be one witness selected, and sometimes all four.

This is like a mathematical puzzle. Any "form referencing" SQL statement is. So, you have to write a statement that covers all possibilities. Is the one you have now adequate? Does it give you what you want?

The one I wrote covered the need for the possible combination that you listed in post #3, that's all. Nothing else.

Have you found the answer you need yet?

 

[dystopias]

I tried adding the "is null" part, but when I did that it returned every record (aka, didn't filter anything) - It makes sense to me, but is not working, so i'm doing something wrong.

 

[ajetrumpet]

This is a complicated issue to explain. I am probably going to write a FAQ on it pretty soon.

How about uploading the sample that you have done this with? It would, indeed, be easier for me to help you figure out the problem...

 

[dystopias]

This is a complicated issue to explain. I am probably going to write a FAQ on it pretty soon.

How about uploading the sample that you have done this with? It would, indeed, be easier for me to help you figure out the problem...

i isolated the form, report, and two tables, and changed the data (solely for confidentiality) -

the report still needs some work, as in, displaying the names of the witnesses selected at top, which i just haven't done yet. i'm just trying to get it to display the right records.

again, if you select john and adam from the form it should pull up every record that have both john and adam in the table witness fields. (it's ok if they have more witnesses than just the two, but two of them have to be john and adam.)

thanks

 

[ajetrumpet]

Use this SQL instead:

SELECT * FROM grandditchexhibitlist WHERE 

([witness_1]=forms!frmrunreports!witness_1 Or
   [witness_2]=forms!frmrunreports!witness_1 Or
      [witness_3]=forms!frmrunreports!witness_1 Or
         [witness_4]=forms!frmrunreports!witness_1 Or
            forms!frmrunreports!witness_1 Is Null) AND

([witness_1]=forms!frmrunreports!witness_2 Or
   [witness_2]=forms!frmrunreports!witness_2 Or
      [witness_3]=forms!frmrunreports!witness_2 Or
         [witness_4]=forms!frmrunreports!witness_2 Or
            forms!frmrunreports!witness_2 Is Null) AND

([witness_1]=forms!frmrunreports!witness_3 Or
   [witness_2]=forms!frmrunreports!witness_3 Or
      [witness_3]=forms!frmrunreports!witness_3 Or
         [witness_4]=forms!frmrunreports!witness_3 Or
            forms!frmrunreports!witness_3 Is Null) AND

([witness_1]=forms!frmrunreports!witness_4 Or
   [witness_2]=forms!frmrunreports!witness_4 Or
      [witness_3]=forms!frmrunreports!witness_4 Or
         [witness_4]=forms!frmrunreports!witness_4 Or
            forms!frmrunreports!witness_4 Is Null);

You had it all messed up! There wasn't even a pattern present.

Anyway, use the statement above to return records that contain any names that you put in any of the combo boxes.

In other words, the returns you get will contain every single name you enter in any or all of the combo boxes. I sure hope this is what you need!

 

[dystopias]

my god, you're a genius.

thanks so much for helping me, i really appreciate it. I'm learning . . . slowly but surely.

 

[ajetrumpet]

my god, you're a genius.

thanks so much for helping me, i really appreciate it.

No problem! That's what we're here for, right?