Wild Card in a Querie

[Chris Gorham]

Hi,

I'm battling here...

I'm trying to write a simple querie that selects either an individual region
or all regions. The source of the selection is a drop down in a form.

Here is the if statment in the qeurie:
IIf([forms]![Main Menu]![Region_Select]<>"All Regions",[forms]![Main
Menu]![Region_Select],Like "*")

I can get it to work when selecting an individual region - no probs. However
when selecting "All Regions" then ideally I just wanted a wild card search
that would include everything.

The regions themselves have variable number of characters and are sometimes
2 words...but try as I might I cannot get this to work...

Any help appreciated...Chris

 

[Jeff Boyce]

Chris

If you are using a form's combobox to let the user select a region, one
possibility would be to allow the user to select NO region to get them all.
Have you tried something like the following in the selection criterion of
the query:?

Like * & Forms![Main Menu]![Region_Select]

If Access can handle the concatenation of the "*" wildcard and a null value
from the form, this seems like it would do the job.


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[John Spencer]

Try moving the LIKE out of the IIF function.

LIKE IIf([forms]![Main Menu]![Region_Select]<>"All
Regions",[forms]![Main Menu]![Region_Select],"*")

Like without wildcards will act like an equals.

This will return all records as long as the Region field has a value.
If any records have null (blank) for the region, they will not be returned.

In that case you can often use criteria like the following:

[forms]![Main Menu]![Region_Select]="All Regions" OR
[forms]![Main Menu]![Region_Select]

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================