Pull criteria from a Form

  • Thread starter Thread starter QB
  • Start date Start date
Q

QB

I set a query to pull its`criteria from a form using

Forms![FormName].[ControlName]

That said, I now have a twist and need help in addressing it.

The control in question is a combo box which has a listing of values (there
a concept!), but it also have a value='All' which when select I would need no
criteria apply to the query.

I tried applying an IIF to the criteria but it doesn`t work

iif(Forms![FormName].[ControlName]="All", Like
"*",Forms![FormName].[ControlName])

How can I make this work the way I need it to?

Thank you,

QB
 
You cannot have the logical function 'Like' inside of the IIF statement as it
will be treated as if it was text.
Try this --
Like IIF([Forms]![FormName]![ControlName]="All", "*",
[Forms]![FormName]![ControlName])
 
Set the criteria under the field to
Forms![FormName]![ControlName] or Forms![FormName]![ControlName] = "ALL"

In the where clause of the SQL statement that would look like

WHERE ([SomeField] = Forms![FormName]![ControlName] or
Forms![FormName]![ControlName] = "ALL")


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top