G
gary b
Since my last question was answered rather quickly by
several resident gurus, I present this question... for
which there may be no answer!
Scenario:
MainTbl (table): containing 15 fields; this is
my 'database'
SelectCriteriaFrm (form): 15 combo boxes allowing user to
select values for any number of combo boxes. (The combo
boxes mirror the fields of MainTbl.) The idea is to allow
the user to set the query criteria by selecting values in
the combo box(es).
DatabaseProjectQry (query): Uses the same-named 15 fields
as the MainTbl. Criteria defined to use values of
corresponding fields in SelectCriteriaFrm and are all on
the same line. (AND function)
The problem:
It is envisioned that the query criteria be set using the
AND mode. Criteria for Field #1 of the query is set to
match the value selected in combo box #1 on
SelectQueryFrm, criteria for Field #2 of the query is on
the same line as criteria #1 and is set to match the value
selected in combo box #2 on SelectQueryFrm, and so on.
This provides a 'drilldown' query capability. HOWEVER, it
also means that all 15 combo boxes MUST be populated. Any
combo box with a NULL value kills the AND function of the
query.
What I want:
If, for example, the user selects values for only combo
box #1 and #5, I want the query to return ALL dB records
matching the two values -- regardless of the values of the
other fields! All non-populated combo boxes (NULL values)
would be wildcards as far as the AND function is
concerned. Example 2: If the user selects a value for
only combo box #3, I want the query to deliver ALL records
containing criteria #3 value.
What I have tried:
I have tried this expression (adjusted to reflect the
appropriate field name) as the criteria for each field of
the query:
IIf(IsNull([Forms]![SelectQueryFrm]![FieldName]),Like "*",
[Forms]![SelectQueryFrm]![FieldName]
It does not work. Substituting any legitimate MainTbl
value for that field in lieu of Like "*" works fine. I
cannot get the 'wildcard' to be recognized. I'm now half-
bald and started doing drugs again. Is this Mission
Impossible???
Masochists may reply to group or remove NOSPAM for email.
Thanks... any help would be appreciated!
several resident gurus, I present this question... for
which there may be no answer!
Scenario:
MainTbl (table): containing 15 fields; this is
my 'database'
SelectCriteriaFrm (form): 15 combo boxes allowing user to
select values for any number of combo boxes. (The combo
boxes mirror the fields of MainTbl.) The idea is to allow
the user to set the query criteria by selecting values in
the combo box(es).
DatabaseProjectQry (query): Uses the same-named 15 fields
as the MainTbl. Criteria defined to use values of
corresponding fields in SelectCriteriaFrm and are all on
the same line. (AND function)
The problem:
It is envisioned that the query criteria be set using the
AND mode. Criteria for Field #1 of the query is set to
match the value selected in combo box #1 on
SelectQueryFrm, criteria for Field #2 of the query is on
the same line as criteria #1 and is set to match the value
selected in combo box #2 on SelectQueryFrm, and so on.
This provides a 'drilldown' query capability. HOWEVER, it
also means that all 15 combo boxes MUST be populated. Any
combo box with a NULL value kills the AND function of the
query.
What I want:
If, for example, the user selects values for only combo
box #1 and #5, I want the query to return ALL dB records
matching the two values -- regardless of the values of the
other fields! All non-populated combo boxes (NULL values)
would be wildcards as far as the AND function is
concerned. Example 2: If the user selects a value for
only combo box #3, I want the query to deliver ALL records
containing criteria #3 value.
What I have tried:
I have tried this expression (adjusted to reflect the
appropriate field name) as the criteria for each field of
the query:
IIf(IsNull([Forms]![SelectQueryFrm]![FieldName]),Like "*",
[Forms]![SelectQueryFrm]![FieldName]
It does not work. Substituting any legitimate MainTbl
value for that field in lieu of Like "*" works fine. I
cannot get the 'wildcard' to be recognized. I'm now half-
bald and started doing drugs again. Is this Mission
Impossible???
Masochists may reply to group or remove NOSPAM for email.
Thanks... any help would be appreciated!