Query Not Generating Expected Results

  • Thread starter Thread starter Neil Chelo
  • Start date Start date
N

Neil Chelo

I have a report based on a query. The query looks at an open forms unbound
cbobox to determine the querys criteria. If the cbobox is null then the
criteria will be set to a specific employees ID# otherwise it will use the
ID# from the cbobox. This works fine unless I want to add more ID#'s to the
criteria if the combo box is null. See below.


IIf([Forms]![frmFundNotesList]![cboNotesFrom] Is
Null,3398,[forms]![frmFundNotesList]![cboNotesFrom]) This IIF statement
works fine. It displays all records from employee id 3398

IIf([Forms]![frmFundNotesList]![cboNotesFrom] Is Null,3398 or 3410 or
3423,[forms]![frmFundNotesList]![cboNotesFrom])
This IIF statement does not generate any results. Why won't this second
statement work?
 
I have a report based on a query. The query looks at an open forms unbound
cbobox to determine the querys criteria. If the cbobox is null then the
criteria will be set to a specific employees ID# otherwise it will use the
ID# from the cbobox. This works fine unless I want to add more ID#'s to the
criteria if the combo box is null. See below.


IIf([Forms]![frmFundNotesList]![cboNotesFrom] Is
Null,3398,[forms]![frmFundNotesList]![cboNotesFrom]) This IIF statement
works fine. It displays all records from employee id 3398

IIf([Forms]![frmFundNotesList]![cboNotesFrom] Is Null,3398 or 3410 or
3423,[forms]![frmFundNotesList]![cboNotesFrom])
This IIF statement does not generate any results. Why won't this second
statement work?
Click to expand...

Because you cannot pass an *operator* such as OR as a search
criterion.

I'd suggest a somewhat different approach: use a WHERE clause like

WHERE ([ID#] IN (3398, 3410, 3423) AND
[Forms]![frmFundNotesList]![cboNotesFrom] Is Null)
OR
[ID#] = [Forms]![frmFundNotesList]![cboNotesFrom]
 
Thanks for your help John,

I got the following error when I put that statement in the criterion.
"Undefined function 'Where' in the expression".

Should I be putting this where clause somewhere else besides the query since
I can not pass an operator in the criterion? If so, where? In the query
filter? The report filter? The actual cbobox on my report?


John Vinson said:
I have a report based on a query. The query looks at an open forms unbound
cbobox to determine the querys criteria. If the cbobox is null then the
criteria will be set to a specific employees ID# otherwise it will use the
ID# from the cbobox. This works fine unless I want to add more ID#'s to the
criteria if the combo box is null. See below.


IIf([Forms]![frmFundNotesList]![cboNotesFrom] Is
Null,3398,[forms]![frmFundNotesList]![cboNotesFrom]) This IIF statement
works fine. It displays all records from employee id 3398

IIf([Forms]![frmFundNotesList]![cboNotesFrom] Is Null,3398 or 3410 or
3423,[forms]![frmFundNotesList]![cboNotesFrom])
This IIF statement does not generate any results. Why won't this second
statement work?
Click to expand...

Because you cannot pass an *operator* such as OR as a search
criterion.

I'd suggest a somewhat different approach: use a WHERE clause like

WHERE ([ID#] IN (3398, 3410, 3423) AND
[Forms]![frmFundNotesList]![cboNotesFrom] Is Null)
OR
[ID#] = [Forms]![frmFundNotesList]![cboNotesFrom]
Click to expand...
 
Thanks for your help John,

I got the following error when I put that statement in the criterion.
"Undefined function 'Where' in the expression".

Should I be putting this where clause somewhere else besides the query since
I can not pass an operator in the criterion? If so, where? In the query
filter? The report filter? The actual cbobox on my report?
Click to expand...

Sorry- didn't make that clear!

I was suggesting that you go into the SQL view of the query and
replace the WHERE clause with what I suggested. The query design grid
is pretty good but it's just a tool to build SQL strings, and this is
getting toward the limits of its abilities!
 
Back
Top