Criteria referencing Form ComboBox

B

briank

My query is trying to reference an open form's combo box in the criteria
section. I am trying (with little luck) to put in an IIF statement to alter
the outcome in the query. When I run this query, I am getting zero records
returned. The form's combo box is set on "All Chapters" in hope of pulling
up all records. Is there a piece of logic that I missing when it comes to
referencing combo boxes.

IIf([Forms]![frmConstDataSheet]![cboChapter]="All Chapters",Is Not
Null,[Forms]![frmConstDataSheet]![cboChapter])
 
B

briank

Addendum: I have tried multiple variations of this iif statement and I feel
that the "is not null" is that part that is not working properly. Any
thoughts?
 
F

fredg

My query is trying to reference an open form's combo box in the criteria
section. I am trying (with little luck) to put in an IIF statement to alter
the outcome in the query. When I run this query, I am getting zero records
returned. The form's combo box is set on "All Chapters" in hope of pulling
up all records. Is there a piece of logic that I missing when it comes to
referencing combo boxes.

IIf([Forms]![frmConstDataSheet]![cboChapter]="All Chapters",Is Not
Null,[Forms]![frmConstDataSheet]![cboChapter])
Click to expand...

Like If([Forms]![frmConstDataSheet]![cboChapter]="All Chapters","*"
,[Forms]![frmConstDataSheet]![cboChapter])
 
B

briank

Your suggestions worked perfectly. I must file away for future issues. TY

fredg said:
My query is trying to reference an open form's combo box in the criteria
section. I am trying (with little luck) to put in an IIF statement to alter
the outcome in the query. When I run this query, I am getting zero records
returned. The form's combo box is set on "All Chapters" in hope of pulling
up all records. Is there a piece of logic that I missing when it comes to
referencing combo boxes.

IIf([Forms]![frmConstDataSheet]![cboChapter]="All Chapters",Is Not
Null,[Forms]![frmConstDataSheet]![cboChapter])
Click to expand...

Like If([Forms]![frmConstDataSheet]![cboChapter]="All Chapters","*"
,[Forms]![frmConstDataSheet]![cboChapter])
Click to expand...
 
B

briank

How would I go about modifying your example if I also wanted to pull up null
values? Something like below?

Like IIf([Forms]![frmConstDataSheet]![cboChapter]="All Chapters","*" or Is
Null
,[Forms]![frmConstDataSheet]![cboChapter])

fredg said:
My query is trying to reference an open form's combo box in the criteria
section. I am trying (with little luck) to put in an IIF statement to alter
the outcome in the query. When I run this query, I am getting zero records
returned. The form's combo box is set on "All Chapters" in hope of pulling
up all records. Is there a piece of logic that I missing when it comes to
referencing combo boxes.

IIf([Forms]![frmConstDataSheet]![cboChapter]="All Chapters",Is Not
Null,[Forms]![frmConstDataSheet]![cboChapter])
Click to expand...

Like If([Forms]![frmConstDataSheet]![cboChapter]="All Chapters","*"
,[Forms]![frmConstDataSheet]![cboChapter])
Click to expand...
 
J

John Spencer

WHERE (SomeField = [Forms]![frmConstDataSheet]![cboChapter] OR
[Forms]![frmConstDataSheet]![cboChapter] = "All Chapters")


IF you enter the following as criteria under your field, Access will
restructure it when it is saved, but the query should work UNLESS you
try too many of this type of criteria at one time

= [Forms]![frmConstDataSheet]![cboChapter] OR
[Forms]![frmConstDataSheet]![cboChapter] = "All Chapters"


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

How would I go about modifying your example if I also wanted to pull up null
values? Something like below?

Like IIf([Forms]![frmConstDataSheet]![cboChapter]="All Chapters","*" or Is
Null
,[Forms]![frmConstDataSheet]![cboChapter])

fredg said:
My query is trying to reference an open form's combo box in the criteria
section. I am trying (with little luck) to put in an IIF statement to alter
the outcome in the query. When I run this query, I am getting zero records
returned. The form's combo box is set on "All Chapters" in hope of pulling
up all records. Is there a piece of logic that I missing when it comes to
referencing combo boxes.

IIf([Forms]![frmConstDataSheet]![cboChapter]="All Chapters",Is Not
Null,[Forms]![frmConstDataSheet]![cboChapter])
Click to expand...
Like If([Forms]![frmConstDataSheet]![cboChapter]="All Chapters","*"
,[Forms]![frmConstDataSheet]![cboChapter])
Click to expand...
Click to expand...
 
Top