Show all values in criteria

philwickens

New member
Local time
Today, 07:34
Joined
Jan 3, 2006
Messages
5
I have searched similar posts to solve this, but havn't been able to make it work yet, so apologies if this sounds like an existing post.

On my form (frmContacts) I have a combo box (LstCompany) that lists companies. The default value is set to "N" (so this is the value when nothing is selected).

I have a query (qryContacts) that retrieves records of People and their Companies from a single table (tblContacts - this table includes the fields 'Person' and 'Company'. Some people have no company). I want this query to:

- When no company is selected in LstCompany: show all people (whether or not they have a company).
- When a company is selected in LstCompany: show records for that company.

I have added the following expression to my query:
Expr1: IIf(([Forms]![frmContacts]![LstCompany])="N",True,([Forms]![frmContacts]![LstCompany]))

Criteria = True


This works fine for showing all records, but the Expr1 field returns #Error if a Company is selected.

Any idea what I am doing wrong, or what will work?
 
Last edited:
Could you make the default a blank then put
Like "*" & [Forms]![frmContacts]![LstCompany]
in the criteria for the company in the query

Brian
 
This works when a company is selected, but when one is not selected it just returns those contacts that have a company and not those that have no company.
 
The Like operator can't return null values.


Put the criteria in a column in the query grid like this:-
-----------------------------------------

Field: IIf([Forms]![frmContacts]![LstCompany]="N", True, [Company]=[Forms]![frmContacts]![LstCompany])

Show: uncheck

Criteria: True

-----------------------------------------
.
 

Users who are viewing this thread

Back
Top Bottom