IIF Function

  • Thread starter Thread starter Mike H
  • Start date Start date
M

Mike H

Aim: To create a field, ST, in a query, the data in the field to be
determined by data in two other fields. i.e. If field Class = B then
field ST= field Time * ER

Existing field: Class has text data in the form of either, A; B; C; D;
Existing field: Time has number data in form of 220.20; 340.45 etc.
Existing field: ER has number data in form 0.987; 0.673 etc.
Required field: ST, to have data in number format similar to Time field.

I can enter an IIF function in a Select query design Field grid such as:
ST: IIf([Class]='B',[Time]*0.97974)
and this works giving me a result in the ST field record associated with
the Class field data 'B'

But, I want to modify the above expression so that it can handle more
than one value of the Class field calculating by a different value of
the Time field. I have tried:-

ST: IIf([Class]='B',[Time]*0.987) OR ST: IIf([Class]='C',[Time]*0.673)

This results in -1 being entered in the records associated with the
Classes 'B' and 'C'

I get the impression that the IIF function should solve the problem but
that I am using it either in the wrong type of query or in the wrong
context.

I hope the above is clear enough for someone to offer a suggestion as to
what to enter and where to put it, (genuine offers only please!!).

Regards
Mike H.
 
Mike,

Wrong way to combine multiple IIf's... the way it is, it's like saying
(in plain english) If Class is B or Class is C, so obviously for B and C
records this evaluates to condition satisfied, i.e. True, represented
with -1 in a numeric field! For the same reason, the same expression
evaluates to 0 (false) in all other records, right?

Now, how to get it done: if I read correctly between the lines, you have
a Class dependent multiplier like:
A => ???
B => 0.987
C => 0.673
etc.
Is this correct? If it is, then you can either use an expression like:

ST: [Time]* (IIf([Class]='A', ???, 0) + IIf([Class]='B', 0.987, 0) +
IIf([Class]='C', 0.673, 0) + ...)

Alternative (a neater solution), you can make a simple two-field table
with the Class and multiplier, include that in your query design (joined
to the main table on Class), and perform a simple multiplication in the
calculated field:

ST: [Time] * [Multiplier]

The latter solution is faster in execution (not obvious with small
tables), but it also has the additional merit of allowing additions and
changes through the table anytime, without having to change the design.

Note: Time is not a good choice for a field name; it is a reserved
Access keyword, and may get you into trouble... suggest you change it.

HTH,
Nikos
 
In message <[email protected]>, Nikos Yannacopoulos

Nikos,
Many thanks for the answer, that's it exactly and confirms a snippet I
picked up from this group whilst trawling after I had posted, regarding
the '+' bit, thank you again.
Regards
Mike H.
Wrong way to combine multiple IIf's... the way it is, it's like saying
(in plain english) If Class is B or Class is C, so obviously for B and
C records this evaluates to condition satisfied, i.e. True, represented
with -1 in a numeric field! For the same reason, the same expression
evaluates to 0 (false) in all other records, right?
Click to expand...
Correct,

<snip>
 
Back
Top