IIF statement in query calculated field

  • Thread starter Thread starter jmoore
  • Start date Start date
J

jmoore

In a calculated field of a query, I am attempting to show the following when
the field CaseCost=3. The DateDiff is calculating only on those cases where
the casecost equals 3, but it is also calculating when the field equals
1/1/7777 and 1/1/9999, instead of displaying the symbols. Thanks for any
suggestions.

1) NA when F12 AND F13 are 1/1/7777 or 1/1/9999
2) $ when F12 is not equal to either, (and therefore is a valid date) AND
F13 equals either 1/1/7777 or 1/1/9999
3) * when F13 is not equal to either, (and therefore is a valid date) AND
F12 equals either 1/1/7777 or 1/1/9999
4) Calculate the difference between two valid dates.

Level3: IIf([CaseCost]=3,IIf(([F12]=(#1/1/7777# Or #1/1/9999#) And
[F13]=(#1/1/7777# Or #1/1/9999#)),"NA",IIf(([F12]<>(#1/1/7777# Or #1/1/9999#)
And [F13]=(#1/1/7777# Or #1/1/9999#)),"$",IIf(([F13]<>(#1/1/7777# Or
#1/1/9999#) And [F12]=(#1/1/7777# Or #1/1/9999#)),"*",
DateDiff("d",[F13],[F12])))))
 
iif( caseCost <> 3, "?",
iif( f12 IN(#1/1/7777#, #1/1/9999#) AND f13 IN(#1/1/7777#,
#1/1/9999#) , "NA",
iif( f12 IN(#1/1/7777#, #1/1/9999#) , "*",
iif( f13 IN(#1/1/7777#, #1/1/9999#) , "$", dateDiff(
"d", f13, f12))
)
)



Note that you were missing the else case for iif( caseCost =3, ...,
esleCase ), which I decided to make a ?

I also replaced the = (... OR ... ), not correct withing SQL, with an IN
( ..., ... ) construction, which is OK.

It is also useless, in my opinion, to test:


f12 IN(#1/1/7777#, #1/1/9999#) AND NOT f13 IN( ... ) , "*"


since we previously checked that they were both "IN", and we would be in
this part of the iif only if one (or none ) of these two "is not in" the
list of special dates.



Vanderghast, Access MVP
 
I am not familiary with IN. Can you give a brief explanation, please. In
your example, the second IIf needs to be and AND statement to show that in
addition to the F12 criteria, F13 does NOT equal either of them (and vice
versa). I'm not sure how to do that with the IN. By the way, the database I
am using is Access 2000 format if that makes a difference.

Thank you.

Michel Walsh said:
iif( caseCost <> 3, "?",
iif( f12 IN(#1/1/7777#, #1/1/9999#) AND f13 IN(#1/1/7777#,
#1/1/9999#) , "NA",
iif( f12 IN(#1/1/7777#, #1/1/9999#) , "*",
iif( f13 IN(#1/1/7777#, #1/1/9999#) , "$", dateDiff(
"d", f13, f12))
)
)



Note that you were missing the else case for iif( caseCost =3, ...,
esleCase ), which I decided to make a ?

I also replaced the = (... OR ... ), not correct withing SQL, with an IN
( ..., ... ) construction, which is OK.

It is also useless, in my opinion, to test:


f12 IN(#1/1/7777#, #1/1/9999#) AND NOT f13 IN( ... ) , "*"


since we previously checked that they were both "IN", and we would be in
this part of the iif only if one (or none ) of these two "is not in" the
list of special dates.



Vanderghast, Access MVP



jmoore said:
In a calculated field of a query, I am attempting to show the following
when
the field CaseCost=3. The DateDiff is calculating only on those cases
where
the casecost equals 3, but it is also calculating when the field equals
1/1/7777 and 1/1/9999, instead of displaying the symbols. Thanks for any
suggestions.

1) NA when F12 AND F13 are 1/1/7777 or 1/1/9999
2) $ when F12 is not equal to either, (and therefore is a valid date) AND
F13 equals either 1/1/7777 or 1/1/9999
3) * when F13 is not equal to either, (and therefore is a valid date) AND
F12 equals either 1/1/7777 or 1/1/9999
4) Calculate the difference between two valid dates.

Level3: IIf([CaseCost]=3,IIf(([F12]=(#1/1/7777# Or #1/1/9999#) And
[F13]=(#1/1/7777# Or #1/1/9999#)),"NA",IIf(([F12]<>(#1/1/7777# Or
#1/1/9999#)
And [F13]=(#1/1/7777# Or #1/1/9999#)),"$",IIf(([F13]<>(#1/1/7777# Or
#1/1/9999#) And [F12]=(#1/1/7777# Or #1/1/9999#)),"*",
DateDiff("d",[F13],[F12])))))
Click to expand...
Click to expand...
 
x IN(a, b, c)

is the same, but shorter, as

x=a OR x=b OR x=c


In other words, is the value in x equal to either one of the supplied
values, or is x value IN the list of supplied values.

While

NOT x IN(a, b, c)

is the same as

x<>a AND x<>b AND x<>c

in other word, is the value hold in x not equal to any values supplied in
the list.



So

f1 IN( #1/1/7777#, #1/1/9999#) AND f2 IN( #1/1/777#, #1/1/9999#)

is the same as

(f1 = #1/1/7777# OR f1 = #1/1/9999#) AND (f2 = #1/1/7777# OR f2
= #1/1/9999#)


while


f1 IN( #1/1/7777#, #1/1/9999#) AND NOT f2 IN( #1/1/777#,
#1/1/9999#)

is the same as

(f1 = #1/1/7777# OR f1 = #1/1/9999#) AND (f2 <> #1/1/7777# AND
f2 <> #1/1/9999#)





Vanderghast, Access MVP



jmoore said:
I am not familiary with IN. Can you give a brief explanation, please. In
your example, the second IIf needs to be and AND statement to show that in
addition to the F12 criteria, F13 does NOT equal either of them (and vice
versa). I'm not sure how to do that with the IN. By the way, the
database I
am using is Access 2000 format if that makes a difference.

Thank you.

Michel Walsh said:
iif( caseCost <> 3, "?",
iif( f12 IN(#1/1/7777#, #1/1/9999#) AND f13 IN(#1/1/7777#,
#1/1/9999#) , "NA",
iif( f12 IN(#1/1/7777#, #1/1/9999#) , "*",
iif( f13 IN(#1/1/7777#, #1/1/9999#) , "$", dateDiff(
"d", f13, f12))
)
)



Note that you were missing the else case for iif( caseCost =3, ...,
esleCase ), which I decided to make a ?

I also replaced the = (... OR ... ), not correct withing SQL, with an
IN
( ..., ... ) construction, which is OK.

It is also useless, in my opinion, to test:


f12 IN(#1/1/7777#, #1/1/9999#) AND NOT f13 IN( ... ) , "*"


since we previously checked that they were both "IN", and we would be in
this part of the iif only if one (or none ) of these two "is not in" the
list of special dates.



Vanderghast, Access MVP



jmoore said:
In a calculated field of a query, I am attempting to show the following
when
the field CaseCost=3. The DateDiff is calculating only on those cases
where
the casecost equals 3, but it is also calculating when the field equals
1/1/7777 and 1/1/9999, instead of displaying the symbols. Thanks for
any
suggestions.

1) NA when F12 AND F13 are 1/1/7777 or 1/1/9999
2) $ when F12 is not equal to either, (and therefore is a valid date)
AND
F13 equals either 1/1/7777 or 1/1/9999
3) * when F13 is not equal to either, (and therefore is a valid date)
AND
F12 equals either 1/1/7777 or 1/1/9999
4) Calculate the difference between two valid dates.

Level3: IIf([CaseCost]=3,IIf(([F12]=(#1/1/7777# Or #1/1/9999#) And
[F13]=(#1/1/7777# Or #1/1/9999#)),"NA",IIf(([F12]<>(#1/1/7777# Or
#1/1/9999#)
And [F13]=(#1/1/7777# Or #1/1/9999#)),"$",IIf(([F13]<>(#1/1/7777# Or
#1/1/9999#) And [F12]=(#1/1/7777# Or #1/1/9999#)),"*",
DateDiff("d",[F13],[F12])))))
Click to expand...
Click to expand...
Click to expand...
 
Michel - Thanks for the mini tutorial. It was very helpful. I was able to
put it all together and the query works like a charm. Many, many thanks.
Joan

Michel Walsh said:
x IN(a, b, c)

is the same, but shorter, as

x=a OR x=b OR x=c


In other words, is the value in x equal to either one of the supplied
values, or is x value IN the list of supplied values.

While

NOT x IN(a, b, c)

is the same as

x<>a AND x<>b AND x<>c

in other word, is the value hold in x not equal to any values supplied in
the list.



So

f1 IN( #1/1/7777#, #1/1/9999#) AND f2 IN( #1/1/777#, #1/1/9999#)

is the same as

(f1 = #1/1/7777# OR f1 = #1/1/9999#) AND (f2 = #1/1/7777# OR f2
= #1/1/9999#)


while


f1 IN( #1/1/7777#, #1/1/9999#) AND NOT f2 IN( #1/1/777#,
#1/1/9999#)

is the same as

(f1 = #1/1/7777# OR f1 = #1/1/9999#) AND (f2 <> #1/1/7777# AND
f2 <> #1/1/9999#)





Vanderghast, Access MVP



jmoore said:
I am not familiary with IN. Can you give a brief explanation, please. In
your example, the second IIf needs to be and AND statement to show that in
addition to the F12 criteria, F13 does NOT equal either of them (and vice
versa). I'm not sure how to do that with the IN. By the way, the
database I
am using is Access 2000 format if that makes a difference.

Thank you.

Michel Walsh said:
iif( caseCost <> 3, "?",
iif( f12 IN(#1/1/7777#, #1/1/9999#) AND f13 IN(#1/1/7777#,
#1/1/9999#) , "NA",
iif( f12 IN(#1/1/7777#, #1/1/9999#) , "*",
iif( f13 IN(#1/1/7777#, #1/1/9999#) , "$", dateDiff(
"d", f13, f12))
)
)



Note that you were missing the else case for iif( caseCost =3, ...,
esleCase ), which I decided to make a ?

I also replaced the = (... OR ... ), not correct withing SQL, with an
IN
( ..., ... ) construction, which is OK.

It is also useless, in my opinion, to test:


f12 IN(#1/1/7777#, #1/1/9999#) AND NOT f13 IN( ... ) , "*"


since we previously checked that they were both "IN", and we would be in
this part of the iif only if one (or none ) of these two "is not in" the
list of special dates.



Vanderghast, Access MVP



In a calculated field of a query, I am attempting to show the following
when
the field CaseCost=3. The DateDiff is calculating only on those cases
where
the casecost equals 3, but it is also calculating when the field equals
1/1/7777 and 1/1/9999, instead of displaying the symbols. Thanks for
any
suggestions.

1) NA when F12 AND F13 are 1/1/7777 or 1/1/9999
2) $ when F12 is not equal to either, (and therefore is a valid date)
AND
F13 equals either 1/1/7777 or 1/1/9999
3) * when F13 is not equal to either, (and therefore is a valid date)
AND
F12 equals either 1/1/7777 or 1/1/9999
4) Calculate the difference between two valid dates.

Level3: IIf([CaseCost]=3,IIf(([F12]=(#1/1/7777# Or #1/1/9999#) And
[F13]=(#1/1/7777# Or #1/1/9999#)),"NA",IIf(([F12]<>(#1/1/7777# Or
#1/1/9999#)
And [F13]=(#1/1/7777# Or #1/1/9999#)),"$",IIf(([F13]<>(#1/1/7777# Or
#1/1/9999#) And [F12]=(#1/1/7777# Or #1/1/9999#)),"*",
DateDiff("d",[F13],[F12])))))
Click to expand...
Click to expand...
Click to expand...
 
MGFoster said:
jmoore said:
In a calculated field of a query, I am attempting to show the following when
the field CaseCost=3. The DateDiff is calculating only on those cases where
the casecost equals 3, but it is also calculating when the field equals
1/1/7777 and 1/1/9999, instead of displaying the symbols. Thanks for any
suggestions.

1) NA when F12 AND F13 are 1/1/7777 or 1/1/9999
2) $ when F12 is not equal to either, (and therefore is a valid date) AND
F13 equals either 1/1/7777 or 1/1/9999
3) * when F13 is not equal to either, (and therefore is a valid date) AND
F12 equals either 1/1/7777 or 1/1/9999
4) Calculate the difference between two valid dates.

Level3: IIf([CaseCost]=3,IIf(([F12]=(#1/1/7777# Or #1/1/9999#) And
[F13]=(#1/1/7777# Or #1/1/9999#)),"NA",IIf(([F12]<>(#1/1/7777# Or #1/1/9999#)
And [F13]=(#1/1/7777# Or #1/1/9999#)),"$",IIf(([F13]<>(#1/1/7777# Or
#1/1/9999#) And [F12]=(#1/1/7777# Or #1/1/9999#)),"*",
DateDiff("d",[F13],[F12])))))
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The expression (#1/1/7777# OR #1/1/9999#) performs a logical OR on the 2
dates (which evaluates to the number 3007477, which is interpreted as a
True value) it does NOT evaluate if F12 equals either of the two dates.
The correct expression is:

(F12 = #1/1/7777# OR F12 = #1/1/9999#)

It might be easier to use the Switch() function than the multiple IIf()
functions (I'll break it up into separate lines for clarity):

IIf(CaseCost=3, Switch(
(F12=#1/1/7777# OR F12=#1/1/9999#)
AND (F13=#1/1/7777# OR F13=#1/1/9999#), "NA",
F12<>#1/1/7777# AND F12<>#1/1/9999#
AND (F13=#1/1/7777# OR F13=#1/1/9999#), "$",
F13<>#1/1/7777# AND F13<>#1/1/9999#
AND (F12=#1/1/7777# OR F12=#1/1/9999#), "*",
DateDiff("d", F13, F12)), NULL)

When you want to know if one variable does NOT equal 2 other values you
have to use the AND, not the OR.

You don't have an expression for when CaseCost <> 3 - the above IIf()
will return NULL in that case.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSZtDn4echKqOuFEgEQKmGACgxPROIiy1vx/rISsoL2HYqdtqHUAAn2lb
dHMEG5+DfKffQE0Ge86Etw1v
=JSDT
-----END PGP SIGNATURE-----
Click to expand...
Thanks for your reply. I added the CaseCost <>3 and I was able to get this
to work with the IN that Michel Walsh suggested. I'd like to also try using
your suggestion but I don't know what the Switch function is.

A question about your example (copied below) when one variable does not
equal two others. Should the statement for F12 be in parens like F13, or is
parens only used when it is an OR statement?
F12<>#1/1/7777# AND F12<>#1/1/9999# AND (F13=#1/1/7777# OR F13=#1/1/9999#),
"$",
 
Back
Top