IIf question

[Melissa]

I have a director who has to keep track of the age of legal cases, calculated from when the case opened. All cases are weighted according to importance, 1, 2, and 3. The date on which the cases are considered "aged" differs according to the weight.

I'm trying to develop a form for her so that she can enter data and also check data for each case. She gave me her current data in an Excel spreadsheet with the fields that she needs. One of these fields is "Aging Date", meaning the day the case gets "old." (Which is bad.) The formula in that field is =IF(D2=1,F2+360,IF(D2=2,F2+540,IF(D2=3,F2+720,"")) ). Works great. Except I can't duplicate this effort in a single field in the form. I feel like there ought to be a way to do it with an Else-If expression, but nothing I've tried works.

Of course I can run a query and come up with the date for inclusion in a report, but it would be really nice if I could get this in the form somehow.

Any help would be greatly appreciated.

[John_G]

Hi

Using If-then-else:

If D2 = 1 then
something = F2 + 360
elseif D2 = 2 then
something = F2 + 540
else
something = F2 + 720
endif
endif

Using an IIF:

something = IIF(D2 = 1, F2 + 360, IIF( D2 = 2, F2 + 540, F2 + 720))

These assume that for D2, any value other than 1 or 2 results in F2 + 720.

If D2 can be something other than 1, 2 or 3 then the above code has to change a bit.

HTH

John

[rivereridanus]

You're right that you can do this with the If-ElseIf block. The basic block would be like this, but with most of the content replaced. I don't know what you've tried or what any of those cells mean, so...

If ([D2]=1) Then
 [F2] + 360
ElseIf([D2] = 2) Then
 [F2] + 540
ElseIf([D2] = 3) Then
 [F2] + 720
End If

[John_G]

Riveridanus -

Your syntax isn't right - you need to assign the expressions to something, as in Variable = F2 + 360

John

[Melissa]

I'm not sure where to put the code. I have another calculation using an expression right in a field in design mode, not in a module or anything. I can't come up with an IIf expression that works in the AgingDate field.

[John_G]

Hi -

A field in design mode of what? The form?

If D2 and F2 are controls on the form, then setting the Control source property of a textbox to:

= IIF([D2] = 1, [F2] + 360, IIF( [D2] = 2, [F2] + 540, [F2] + 720))

should work. But the textbox cannot be bound to a table field; it shouldn't be in any case, since it is not good database practice to store calculated data in the database. To put it another way, the result of the above expression should not be stored in the same table as D2 and F2; just calculate it when you need to.


HTH

John



John

[Melissa]

OMG I think it worked. Beautiful!!