Multiple IIf's

[Tara]

I have a query that is used to evaluate data from a table with several
fields, 5 of which are used in this query. One field (Visits) shows how many
times staff met with a client. The other 4 fields (Stable, Prepared, Safe,
Responsive) represent how many times the client responded a certain way
during each visit. I need to set up an IIf statement that will evaluate
those fields and display a string based on the evaluations. So far I have it
set up to show "Client had no visits during this period" when there were no
visits. I also need to to evaluate each of the other 4 fields for
intentional blanks. For example, if both Prepared and Stable were not rated
during the visits, the field is left blank. In that case I need it to say
"Client was not rated in one or more areas during this period". Everytime I
try to set it up with Multiple IIf statements though, I get the following
error: "The expression you entered has a function containing the wrong
number of arguments". Here's the statement:

Note: IIf([Visits]=0,"Client had no visits during this period",
IIf([Prepared] Is Null Or IIf([Stable] Is Null Or IIf([Response] Is Null Or
IIf([Safe] Is Null, "The Client was not evaluated in one or more areas during
this period")))))

 

[Dale Fye]

Tara,

I strongly recommend that you create a User Defined Function for this. This
type of business logic is a lot easier to implement in a function. Plus, you
can use remarks in your code to document why you are doing what you are doing.

One of the things I hate most about repairing someone elses database is long
nested IIF( ) statements within a query. Because there is no way to
determine why the person did what they did.

 

[Tara]

Thanks for the tip Dale. Can you give me some additional direction for doing
that? I've done only minimal coding and I'm not very familiar with it.

Tara,

I strongly recommend that you create a User Defined Function for this. This
type of business logic is a lot easier to implement in a function. Plus, you
can use remarks in your code to document why you are doing what you are doing.

One of the things I hate most about repairing someone elses database is long
nested IIF( ) statements within a query. Because there is no way to
determine why the person did what they did.

----
HTH
Dale

I have a query that is used to evaluate data from a table with several
fields, 5 of which are used in this query. One field (Visits) shows how many
times staff met with a client. The other 4 fields (Stable, Prepared, Safe,
Responsive) represent how many times the client responded a certain way
during each visit. I need to set up an IIf statement that will evaluate
those fields and display a string based on the evaluations. So far I have it
set up to show "Client had no visits during this period" when there were no
visits. I also need to to evaluate each of the other 4 fields for
intentional blanks. For example, if both Prepared and Stable were not rated
during the visits, the field is left blank. In that case I need it to say
"Client was not rated in one or more areas during this period". Everytime I
try to set it up with Multiple IIf statements though, I get the following
error: "The expression you entered has a function containing the wrong
number of arguments". Here's the statement:

Note: IIf([Visits]=0,"Client had no visits during this period",
IIf([Prepared] Is Null Or IIf([Stable] Is Null Or IIf([Response] Is Null Or
IIf([Safe] Is Null, "The Client was not evaluated in one or more areas during
this period")))))

 

[Gina Whipp]

Tara,

While I agree with Dale on this... It should be noted that in an IF
statement you need 3 parts...

IF ThisIsTrue THEN DoThat OR DoThisIfNoneOfThePreviousIsTrue

You also use Is Null, however Is Null may or may not apply, so you may not
get the desired results. Do those fields contain any data, such as 0
(zero), in which case Is Null will not work.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Tara,

I strongly recommend that you create a User Defined Function for this.
This
type of business logic is a lot easier to implement in a function. Plus,
you
can use remarks in your code to document why you are doing what you are
doing.

One of the things I hate most about repairing someone elses database is
long
nested IIF( ) statements within a query. Because there is no way to
determine why the person did what they did.

----
HTH
Dale

I have a query that is used to evaluate data from a table with several
fields, 5 of which are used in this query. One field (Visits) shows how
many
times staff met with a client. The other 4 fields (Stable, Prepared,
Safe,
Responsive) represent how many times the client responded a certain way
during each visit. I need to set up an IIf statement that will evaluate
those fields and display a string based on the evaluations. So far I
have it
set up to show "Client had no visits during this period" when there were
no
visits. I also need to to evaluate each of the other 4 fields for
intentional blanks. For example, if both Prepared and Stable were not
rated
during the visits, the field is left blank. In that case I need it to
say
"Client was not rated in one or more areas during this period".
Everytime I
try to set it up with Multiple IIf statements though, I get the following
error: "The expression you entered has a function containing the wrong
number of arguments". Here's the statement:

Note: IIf([Visits]=0,"Client had no visits during this period",
IIf([Prepared] Is Null Or IIf([Stable] Is Null Or IIf([Response] Is Null
Or
IIf([Safe] Is Null, "The Client was not evaluated in one or more areas
during
this period")))))

 

[KARL DEWEY]

Your nesting is wrong. It should be like this --
Note: IIf([Visits]=0,"Client had no visits during this period",
IIf([Prepared] Is Null Or [Stable] Is Null Or [Response] Is Null Or [Safe] Is
Null, "The Client was not evaluated in one or more areas during this period",
"Say something here if those are not null"))

 

[John W. Vinson]

I have a query that is used to evaluate data from a table with several
fields, 5 of which are used in this query. One field (Visits) shows how many
times staff met with a client. The other 4 fields (Stable, Prepared, Safe,
Responsive) represent how many times the client responded a certain way
during each visit.

This report would be a LOT easier if you took one step further in your table
normalization. It seems that you have a one (visit) to many (responses)
relationship; you might want to consider a Responses table, with fields
VisitID, Outcome (e.g. "Stable" or "Responsive"), and Occurances (number
field, what's currently in your Stable field).

Then you could use a very simple totals or Crosstab query to report the total
number of responses.

 

[Dale Fye]

Tara,

I assume you are familar with the VB Editors code window.

1. If you Right click in the project window (mine is in the upper left
corner of the VB Editor), place your mouse over the Insert option, and then
select Module. This will create a new code module. In the Properties
window (mine is in the bottom left) you will see a new name for the module
(probably Module1 or something like that). You can keep that name or give
it a new name, something like mod_BusinessLogic. The important thing here
is that the module name cannot be the same as the function name. I try to
make my module names descriptive, so I know where to look for my code.

2. Now, in the code window, you should already see:

Option Compare Database
Option Explicit

or something like that. I always make sure that these are in every code
module.

3. Right below that you can create a new function to evaluate the data you
want to analyze. Since you have five fields you are interested in, you need
to declare all of those fields, and like Gina mentioned, since some of these
may be NULL, you will need to declare those that might be null as Variant,
so that the function can accept NULL values. So your function might look
something like:

Public Function fnNote(intVisits as integer, varPrepared as variant,
varStable as variant, varResponse as variant, varSafe as variant) as String

if intVisits = 0 then
fnNote = "Client had no visits during this period"
elseif isnull(varPrepared & varStable & varResponse & varSafe) then
'If all of the variables other than intVisits are NULL, then use
this
fnNote = "Client was not evaluated in any area during this period"
else
'Otherwise, check each one individually and add it to the string if
it is null
fnNote = iif(isnull(varPrepared), ", Prepared", "")
if isnull(varStable) then fnNote = fnNote & ", Stable"
if isnull(varResponse) then fnNote = fnNote & ", Response"
if isnull(varSafe) then fnNote = fnNote & ", Safe"

if len(fnNote) = 0 then
'if none of the variables was NULL
fnNote = "Client was evaluate in all areas during this period"
else
'If one or more was NULL, then display exactly which were not
evalueated
fnNote = "Client was not evaluated in the following areas (" &
Mid(fnNote, 2) & ") during this period"
endif
end if

End Function

4. I'm not certain that I have your business logic exactly correct, but you
can see how much more useful and easy to debug that this would be than using
an IIF statement. Then, in your query, you would just use:

Note: fnNote([Visits], [Prepared], [Stable], [Response], [Safe])

HTH
Dale

Thanks for the tip Dale. Can you give me some additional direction for
doing
that? I've done only minimal coding and I'm not very familiar with it.

Tara,

I strongly recommend that you create a User Defined Function for this.
This
type of business logic is a lot easier to implement in a function. Plus,
you
can use remarks in your code to document why you are doing what you are
doing.

One of the things I hate most about repairing someone elses database is
long
nested IIF( ) statements within a query. Because there is no way to
determine why the person did what they did.

----
HTH
Dale

I have a query that is used to evaluate data from a table with several
fields, 5 of which are used in this query. One field (Visits) shows
how many
times staff met with a client. The other 4 fields (Stable, Prepared,
Safe,
Responsive) represent how many times the client responded a certain way
during each visit. I need to set up an IIf statement that will
evaluate
those fields and display a string based on the evaluations. So far I
have it
set up to show "Client had no visits during this period" when there
were no
visits. I also need to to evaluate each of the other 4 fields for
intentional blanks. For example, if both Prepared and Stable were not
rated
during the visits, the field is left blank. In that case I need it to
say
"Client was not rated in one or more areas during this period".
Everytime I
try to set it up with Multiple IIf statements though, I get the
following
error: "The expression you entered has a function containing the wrong
number of arguments". Here's the statement:

Note: IIf([Visits]=0,"Client had no visits during this period",
IIf([Prepared] Is Null Or IIf([Stable] Is Null Or IIf([Response] Is
Null Or
IIf([Safe] Is Null, "The Client was not evaluated in one or more areas
during
this period")))))