If Then, Case, or IIF?

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

Hi,

Here is my problem. I have users who enter information
into a form that computes 2 numbers. Let's call numbers Z
and Y. Let's say Z=39 and Y=19

What I'd like to do is write something that will do the
following.

If Y >= Z display "Dynamic", if not take Z - 12 and
perform Is Y >= Z if yes display "Dynamic2", if not take
Z - 12 again, then perform is Y >= Z, if yes
display "Dynamic3", if not take Z - 12 again, etc.

I can't seem to figure out what method to use.

Thanks.

Jason
 
Try this (not fully tested, so may not be 100% accurate):

DisplayValue = "Dynamic" & IIf(Y>=Z, "", CStr$(Z \ 12 - Y \ 12 + 1))
 
Perhaps:

DisplayValue = "Dynamic" & Iif( Z<=Y, "" , CStr$((Z-Y) \ 12 + 2) )

From Debug window:

Y = 19
Z = 17
?"Dynamic" & Iif( Z<=Y, "" , CStr$((Z-Y) \ 12 + 2) )
Dynamic

Z = 27
?"Dynamic" & Iif( Z<=Y, "" , CStr$((Z-Y) \ 12 + 2) )
Dynamic2

Z = 39
?"Dynamic" & Iif( Z<=Y, "" , CStr$((Z-Y) \ 12 + 2) )
Dynamic3
 
Thanks Ken! Worked like a charm.

Jason

-----Original Message-----
Try this (not fully tested, so may not be 100% accurate):

DisplayValue = "Dynamic" & IIf(Y>=Z, "", CStr$(Z \ 12 - Y \ 12 + 1))


--
Ken Snell
<MS ACCESS MVP>





.
Click to expand...
 
I am not 100% sure of your requirements but be *beware* that Ken's and my
expression give different results in some circumstances. For example:

For Z=35 and Y = 2,

Ken's expression = "Dynamic3"
My expression = "Dynamic4"

Have you done some tests and see which one is suitable for your
requirements?
 
Exactly. I used Ken's becasue that was the most suitable
for my needs. When I tested it orginally, both scenerios
yielded the same result. However, after further testing I
realized they were in fact different.

Now, they changed the requirements on me again. Now, for
this same situation, if Chunk Size <= 3 there needs to be
a generic message displayed such as "Your size is too
small, etc."

How can I fit that into this equation?

DisplayValue: "Dynamic" & IIf([ChunkSize]>=[Number of
Periods],"",CStr$([Number of Periods]\12-[ChunkSize]\12+1))
 
Exactly. I used Ken's becasue that was the most suitable
for my needs. When I tested it orginally, both scenerios
yielded the same result. However, after further testing I
realized they were in fact different.

Now, they changed the requirements on me again. Now, for
this same situation, if Chunk Size <= 3 there needs to be
a generic message displayed such as "Your size is too
small, etc."

How can I fit that into this equation?

DisplayValue: "Dynamic" & IIf([ChunkSize]>=[Number of
Periods],"",CStr$([Number of Periods]\12-[ChunkSize]\12+1))
 
Back
Top