On my MS Access Data Entry form I have two optional group controls.
First is opt_Category with next values:
1 – All
2 – Active
3 - Not Active
4 – In Process
5 – Completed
Second optional group control is opt_City
On Main form I have sub forms (tabs) for each of those City where I enter some values and some dates
Aplication submited Date:
Aplication Process start Date:
Aplication Decision Date:
In lower part of form I have detail sub form where I display results of proper selection combination.
Let say I want to show all applicants regardless of Category or City or dates entered in.
I will show applicants from Category and all of Cities regardless if date entry exist or not exist (as business rule allows to enter – register an applicant without any of dates present in).
If I want to show applicants from Paris and London that have submitted application (Submitted Date Is Not Null) or (Submitted Date is Null but Process Start Date Is Not Null) Business rule allow this kind of entry.
And so on…
What I want to say if I am creating separate query for each of those combination it will take a lot time and every new City added will multiply number of queries .
For now I have four Cities and number of Combination is 32 If I add next city it will go to 64.
How to create this selection criteria on fly? I assume It has to be “where” clause?
And I assume I need to loop through those two option group button to pull out which one is selected and put it in criteria – where clause - of select statement?
Any suggestion and example how to do this?