Complicated Filter

[mungyun]

Hey all, i had no luck at all searching for this example so i hope one of you can help me with it.

Im working on a pretty complex vba project and one of the tasks i have to accomplish is to be able to filter data by 3 or 4 different criteria simultaneously and by multiple values per criteria. Ive made some simpler filters that can filter based on one criteria in each but not multiple like what they want.

Ive attached an excel sheet with some sample data. the first four columns are the data and the rest are the criteria and extract locations. So you can look at that while i give the example so it makes a little more sense

The example they gave me was to be able to get the records that have Certain, Medium, High Probability. No Load and Motor Driven for Type. And For buildings TC-G and TC-K

The best ive came up with is to make many repetitions. You will be able to see what i put in the criteria ranges and the results i got.

I just got thrown into this project last week and nobody else here has any experience with this so i just want to make sure im doing it the best way possible. The way they select the criteria is through listboxes so my hardes part will be looping through their selections and entering them into the criteria area but i think i have what i need for that.

Any advice will greatly be appreciated!

 

[SkipVought]

What's the question?

You posted your Data, the CRITERIA and the results.

What's the question?

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[SkipVought]

I used MS Query.

RESULTS:
[tt]
Quarter Building Probability Type
1Q09 TC-K Medium no load
2Q08 TC-K High motor driven
3Q08 TC-G High motor driven
3Q08 TC-G High motor driven
3Q08 TC-K High motor driven
[/tt]
The SQL, that is generated in the QBE grid...

SELECT `Query$`.Quarter, `Query$`.Building, `Query$`.Probability, `Query$`.Type

FROM `Query$` `Query$`

WHERE (`Query$`.Building In ('TC-G','TC-K'))
  AND (`Query$`.Probability In ('Certain','High','Medium','high'))
  AND (`Query$`.Type In ('motor driven','no load'))

Very simple!!!.

H can help you customize to the LIST selections the user will make.


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}