Pull criteria from a Form

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
 
K

KARL DEWEY

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])
 
J

John Spencer

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Prompting several different criteria from a form (Query by Form) 2
How do I used wildcard characters in Query by Form? 1
Query by form problems 6
criteria for date field using iif() 1
criteria based on a form 1
Using a Form to Feed the Criteria in a Query 2
access 2003 runtime query problem with sub-form criteria 3
please help 7

Top