I damn thee IIF statement.....


J

JJurek

Hello All,

I figured I would pass this one by the throbbing brain and see what results
come up.

I have a form that has 2 dropdown boxes.

One is for a Licensing Specialist Name, the other is for the type of report
to display
(Types being P=Pending, W= Withdrawn, etc)

The IIF statement I am working with is listed below:

IIf([Forms]![Application Status Parameters]![CmbSpecialist]="ALL",[Licensing
Specialist].[Initials] Is Not Null,[Forms]![Application Status
Parameters]![CmbSpecialist])

What I am trying to do is pass the parameters to the Query, make a decision
based on user input and display the report. What I am attempting to
accomplish in the if statement is this: If the user selects "ALL", display
all the licenses for all of the licensing specialists else, display what the
user has selected.

The report runs, displays a few errors and no data.

Thoughts? Need more info?

-J
 
Ad

Advertisements

J

John Spencer

You can't use an IIF statement to choose between two different criteria
statements.

You can rewrite the expression to the following

LIKE IIF([Forms]![Application Status Parameters]![CmbSpecialist]<>"ALL",
[Forms]![Application Status Parameters]![CmbSpecialist],"*")

That will give you a specific specialist or all specialists where the field
has a value. I assume that you are applying the criteria to the Initials
field in both cases.

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

JJurek

John,

Thanks for your help. I completely overlooked "LIKE"

Been a few years since I looked t Access... its coming back to me now.

Thanks again for your help!

-J

John Spencer said:
You can't use an IIF statement to choose between two different criteria
statements.

You can rewrite the expression to the following

LIKE IIF([Forms]![Application Status Parameters]![CmbSpecialist]<>"ALL",
[Forms]![Application Status Parameters]![CmbSpecialist],"*")

That will give you a specific specialist or all specialists where the field
has a value. I assume that you are applying the criteria to the Initials
field in both cases.

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

I figured I would pass this one by the throbbing brain and see what results
come up.

I have a form that has 2 dropdown boxes.

One is for a Licensing Specialist Name, the other is for the type of report
to display
(Types being P=Pending, W= Withdrawn, etc)

The IIF statement I am working with is listed below:

IIf([Forms]![Application Status Parameters]![CmbSpecialist]="ALL",[Licensing
Specialist].[Initials] Is Not Null,[Forms]![Application Status
Parameters]![CmbSpecialist])

What I am trying to do is pass the parameters to the Query, make a decision
based on user input and display the report. What I am attempting to
accomplish in the if statement is this: If the user selects "ALL", display
all the licenses for all of the licensing specialists else, display what the
user has selected.

The report runs, displays a few errors and no data.

Thoughts? Need more info?

-J
 
J

John W. Vinson

Hello All,

I figured I would pass this one by the throbbing brain and see what results
come up.

I have a form that has 2 dropdown boxes.

One is for a Licensing Specialist Name, the other is for the type of report
to display
(Types being P=Pending, W= Withdrawn, etc)

The IIF statement I am working with is listed below:

IIf([Forms]![Application Status Parameters]![CmbSpecialist]="ALL",[Licensing
Specialist].[Initials] Is Not Null,[Forms]![Application Status
Parameters]![CmbSpecialist])

What I am trying to do is pass the parameters to the Query, make a decision
based on user input and display the report. What I am attempting to
accomplish in the if statement is this: If the user selects "ALL", display
all the licenses for all of the licensing specialists else, display what the
user has selected.

The report runs, displays a few errors and no data.

Thoughts? Need more info?

-J

The problem is that you cannot pass *OPERATORS* such as "is not null" as a
Parameter - only actual values.

Try a different approach: use a criterion of

=[Forms]![Application Status Parameters]![CmbSpecialist] OR
[Forms]![Application Status Parameters]![CmbSpecialist] = "ALL"
 
D

Douglas J. Steele

<picky>
There's a subtle difference between

LIKE IIF([Forms]![Application Status Parameters]![CmbSpecialist]<>"ALL",
[Forms]![Application Status Parameters]![CmbSpecialist],"*")

and

=[Forms]![Application Status Parameters]![CmbSpecialist] OR
[Forms]![Application Status Parameters]![CmbSpecialist] = "ALL"

When All is selected in the combo box, the first will ignore any records
where the field has a Null value, the second will include them.
</picky>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


John Spencer said:
You can't use an IIF statement to choose between two different criteria
statements.

You can rewrite the expression to the following

LIKE IIF([Forms]![Application Status Parameters]![CmbSpecialist]<>"ALL",
[Forms]![Application Status Parameters]![CmbSpecialist],"*")

That will give you a specific specialist or all specialists where the
field has a value. I assume that you are applying the criteria to the
Initials field in both cases.

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

I figured I would pass this one by the throbbing brain and see what
results come up.

I have a form that has 2 dropdown boxes.

One is for a Licensing Specialist Name, the other is for the type of
report to display
(Types being P=Pending, W= Withdrawn, etc)

The IIF statement I am working with is listed below:

IIf([Forms]![Application Status
Parameters]![CmbSpecialist]="ALL",[Licensing Specialist].[Initials] Is
Not Null,[Forms]![Application Status Parameters]![CmbSpecialist])

What I am trying to do is pass the parameters to the Query, make a
decision based on user input and display the report. What I am
attempting to accomplish in the if statement is this: If the user
selects "ALL", display all the licenses for all of the licensing
specialists else, display what the user has selected.

The report runs, displays a few errors and no data.

Thoughts? Need more info?

-J
 
J

John Spencer

I realize that but the original posting was trying to test for is not null if
the value of the combobox was "ALL". My suggested solution was based on that.

If the poster had indicated a desire to get ALL records, even those where the
initials field was null, then the second set of criteria is the correct
criteria to use.

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

Advertisements

J

James A. Fortune

JJurek said:
Hello All,

I figured I would pass this one by the throbbing brain and see what results
come up.

I have a form that has 2 dropdown boxes.

One is for a Licensing Specialist Name, the other is for the type of report
to display
(Types being P=Pending, W= Withdrawn, etc)

The IIF statement I am working with is listed below:

IIf([Forms]![Application Status Parameters]![CmbSpecialist]="ALL",[Licensing
Specialist].[Initials] Is Not Null,[Forms]![Application Status
Parameters]![CmbSpecialist])

What I am trying to do is pass the parameters to the Query, make a decision
based on user input and display the report. What I am attempting to
accomplish in the if statement is this: If the user selects "ALL", display
all the licenses for all of the licensing specialists else, display what the
user has selected.

The report runs, displays a few errors and no data.

Thoughts? Need more info?

-J

Try:

WHERE IIf([Forms]![Application Status
Parameters]![CmbSpecialist]="ALL",[Licensing Specialist].[Initials] Is
Not Null,[Licensing Specialist].[SpecialistFieldName] =
[Forms]![Application Status Parameters]![CmbSpecialist])

Note: You should use your actual field name that matches CmdSpecialist
instead of [SpecialistFieldName] as I have shown.

The IIf expression results in a Boolean so it was trying to decide if
your combobox text was True or False. In:

http://groups.google.com/group/microsoft.public.access/browse_frm/thread/9527d1194d9a0523

I said:

"They should work exactly the same. Actually, I don't think I've ever
tried what I did with the IIF version before. It might open up some new
possibilities."

Thanks for finding one of those new possibilities. I applaud your
creativity.

James A. Fortune
(e-mail address removed)
 

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

Top