Case functions nested inside an If function

[turner_marketing]

Can anyone tell me what is wrong with this calcuation:

If(Date?8/1/2003,(Case(Name="Lori", 165, Name="Julie", 99, Name="Carl", 110, Name="Christina", 59, 79)*Hours), (Case(Name="Lori", 165, Name="Julie", 99, Name="Carl", 110, Name="Christina", 59, Name="Jesse", 89, 79)*Hours))

Date, Name and Hours are all names of fields.

What we want to acheive is to change someones rate for time going forward, but not mess up the calculations on time going past. I thought this might work, but I end up with the same rate regardless of the datedate.

[danjacoby]

It's because you're using the same numbers regardless of the date, i.e., Lori=165, Julie=99, etc.

[Dan-A]

What's the question mark(?) between Date and the date typed in the if ?

It looks like the if does not eveluate anything.

like Date = TextToDate("12/04/2003") or

like Date > Date(12, 25, 2003)

--Dan

[-Queue-]

I believe it is either a >= or <= combination that doesn't display on Windows.

[Fitch]

Maybe you want something like:

Hours *

Case(

Name="Lori", 165,

Name="Julie", 99,

Name="Carl", 110,

Name="Christina", 59,

Name="Jesse", Case( Date > 7/31/2003, 89, 79),

79)

You should consider creating a rate field to each person's record -- (Hours * Rate) is pretty simple.

[danjacoby]

My answer is still valid -- if you have:

If(A, B,

You expect to get "B" regardless of the "If". If you want something else, you have to set it up that way.

[Fitch]

Dan, you're right, but if you look closely at the original formula, there is one name that is different. That's how I came up with the formula above.