Criteria for Query IIF clause

[BurtArkin]

I have a query where I want to show either a single selection from a drop
down box, or all records if the dropdown box is null. The language I'm using
is:
IIf(IsNull([Forms]![ReportForm]![cboSCType]),>1,[Forms]![ReportForm]![cboSCType])
When a selection is made in the cboSCType dropdown, everything works. If I
leave the dropdown box null, blank and empty, I get strange and inconsistent
results. (I'm using Access 2002 on an XP Pro system. The elements of the
dropdown box are autonumbered.)
Please tell me what I'm doing wrong. Thanks

 

[KC-Mass]

Hi Burt,

What is the assignment of ">1" meant to do and what result would you expect?

Kevin

 

[BurtArkin]

The >1 refers to the auto id numbers in the SCTypes table. If I select a
type in the drop down box, it works fine as a filter. It's when I leave the
drop down box null that I want all records, unfiltered, so I ask for all
records with an auto id greater than 1.
--
Burt

Hi Burt,

What is the assignment of ">1" meant to do and what result would you expect?

Kevin

I have a query where I want to show either a single selection from a drop
down box, or all records if the dropdown box is null. The language I'm
using
is:
IIf(IsNull([Forms]![ReportForm]![cboSCType]),>1,[Forms]![ReportForm]![cboSCType])
When a selection is made in the cboSCType dropdown, everything works. If
I
leave the dropdown box null, blank and empty, I get strange and
inconsistent
results. (I'm using Access 2002 on an XP Pro system. The elements of the
dropdown box are autonumbered.)
Please tell me what I'm doing wrong. Thanks

 

[BurtArkin]

The >1 refers to the auto id numbers in the SCTypes table. If I select a
type in the drop down box, it works fine as a filter. It's when I leave the
drop down box null that I want all records, unfiltered, so I ask for all
records with an auto id greater than 1.
--
Burt

Hi Burt,

What is the assignment of ">1" meant to do and what result would you expect?

Kevin

I have a query where I want to show either a single selection from a drop
down box, or all records if the dropdown box is null. The language I'm
using
is:
IIf(IsNull([Forms]![ReportForm]![cboSCType]),>1,[Forms]![ReportForm]![cboSCType])
When a selection is made in the cboSCType dropdown, everything works. If
I
leave the dropdown box null, blank and empty, I get strange and
inconsistent
results. (I'm using Access 2002 on an XP Pro system. The elements of the
dropdown box are autonumbered.)
Please tell me what I'm doing wrong. Thanks

 

[golfinray]

IIF statements need to have IIF([field]>1,[what to do if true],[what to do if
false])
So for you, you need to fil in the false part AND there is no comma after
field.

 

[BurtArkin]

IIf(IsNull([Forms]![ReportForm]![cboSCType]),>1,[Forms]![ReportForm]![cboSCType])

The source drop down box is null, which is the condition. What is not
working is the second part, where I want all the records unfiltered.

Thanks
--
Burt

IIF statements need to have IIF([field]>1,[what to do if true],[what to do if
false])
So for you, you need to fil in the false part AND there is no comma after
field.
--
Milton Purdy
ACCESS
State of Arkansas

I have a query where I want to show either a single selection from a drop
down box, or all records if the dropdown box is null. The language I'm using
is:
IIf(IsNull([Forms]![ReportForm]![cboSCType]),>1,[Forms]![ReportForm]![cboSCType])
When a selection is made in the cboSCType dropdown, everything works. If I
leave the dropdown box null, blank and empty, I get strange and inconsistent
results. (I'm using Access 2002 on an XP Pro system. The elements of the
dropdown box are autonumbered.)
Please tell me what I'm doing wrong. Thanks

 

[Douglas J. Steele]

You cannot change a criteria from = to > like that.

Instead, set your criteria to

[Forms]![ReportForm]![cboSCType] OR ([Forms]![ReportForm]![cboSCType] IS
NULL)

 

[BurtArkin]

I'm confused. I thought you could have a query with an iif clause on the
criteria line. That's why I set it to:

IIf(IsNull([Forms]![ReportForm]![cboSCType]),>1,[Forms]![ReportForm]![cboSCType])

where the criterion for a null drop down box is an unfiltered table, or
filtered by the inut in the drop down box. The aim is to get either a
filtered batch of data, or all the data based on the selection or lack
thereof.

If that's fundamentally incorrect--that you cannot have an iif statement on
the criteria line--how do you enable the desired choice?

Thanks again for your response.
--
Burt

You cannot change a criteria from = to > like that.

Instead, set your criteria to

[Forms]![ReportForm]![cboSCType] OR ([Forms]![ReportForm]![cboSCType] IS
NULL)
--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a query where I want to show either a single selection from a drop
down box, or all records if the dropdown box is null. The language I'm
using
is:
IIf(IsNull([Forms]![ReportForm]![cboSCType]),>1,[Forms]![ReportForm]![cboSCType])
When a selection is made in the cboSCType dropdown, everything works. If
I
leave the dropdown box null, blank and empty, I get strange and
inconsistent
results. (I'm using Access 2002 on an XP Pro system. The elements of the
dropdown box are autonumbered.)
Please tell me what I'm doing wrong. Thanks

 

[John Spencer MVP]

You use the method that Mr. Steele suggested. Enter the following as the
criteria under your field.

[Forms]![ReportForm]![cboSCType] OR [Forms]![ReportForm]![cboSCType] IS NULL

This will filter by the value in cboSCType unless the value is null. If the
value is null in cboSCType, then the second part of the expression is true and
ALL records will be returned based on this field. If you have other criteria,
that will still apply.

The secret is that a record is returned if the expression evaluates to true
and it will evaluate to true if either expression is true.

So if
somefield = [Forms]![ReportForm]![cboSCType], that row will be returned
because of the true generated by the expression. OR if
[Forms]![ReportForm]![cboSCType] is null, then you will get TRUE and the row
is returned.

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

 

[BurtArkin]

Sorry about that!! I misread Mr Steele's suggestion. Thank you both for all
your help. I really appreciate you folks "being there" for people like me.
--
Burt

You use the method that Mr. Steele suggested. Enter the following as the
criteria under your field.

[Forms]![ReportForm]![cboSCType] OR [Forms]![ReportForm]![cboSCType] IS NULL

This will filter by the value in cboSCType unless the value is null. If the
value is null in cboSCType, then the second part of the expression is true and
ALL records will be returned based on this field. If you have other criteria,
that will still apply.

The secret is that a record is returned if the expression evaluates to true
and it will evaluate to true if either expression is true.

So if
somefield = [Forms]![ReportForm]![cboSCType], that row will be returned
because of the true generated by the expression. OR if
[Forms]![ReportForm]![cboSCType] is null, then you will get TRUE and the row
is returned.

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

I'm confused. I thought you could have a query with an iif clause on the
criteria line. That's why I set it to:

IIf(IsNull([Forms]![ReportForm]![cboSCType]),>1,[Forms]![ReportForm]![cboSCType])

where the criterion for a null drop down box is an unfiltered table, or
filtered by the inut in the drop down box. The aim is to get either a
filtered batch of data, or all the data based on the selection or lack
thereof.

If that's fundamentally incorrect--that you cannot have an iif statement on
the criteria line--how do you enable the desired choice?

Thanks again for your response.