Syntax of a Formula using Date Part

[Huddle]

I think I have the syntax wrong in the date part area. Can you help?

= IIf(IsNull([Space](IIf([FSL]=4,IIf([CountOfInsp_Date]<DatePart("ww",date()*2),"Under",IIf([CountOfInsp_Date]=<DatePart("ww",date()*2.5),"On Target", "Over"))

[pbaldy]

Offhand I'd say the syntax error is with the IsNull at the beginning, but it makes no sense to multiply the date by 2 or 2.5. What are you trying to achieve?

[Huddle]

If [space] is null and the [FSL] = 4 and you took the week number (i.e. 2 for Jan 11) and times it by 2 (which gives you 4) and the count is less than that then they are under their count. If you times the week by 2.5 (giving you 5 in our example) and they greater than 4 but =< 5 then they are on target, otherwise they are over. I hope this makes sense.

[pbaldy]

You'd want to get the week, then multiply by 2. You're multiplying before getting the week.

[pbaldy]

By the way, what's going to happen later in the year when the week is 30 or 40?

[Huddle]

So it should be <DatePart("ww",date())*2, "In week 52 they should have 104 or more in their count.

[pbaldy]

Yes; does it work?

[Huddle]

it still gives the syntax error. how is the IsNull wrong?

[pbaldy]

The parentheses aren't closed, and it runs right into the IIf() following it.

[Huddle]

Changed it to this but still getting the syntax error.

= IIf(IsNull([Space]),(IIf([FSL]=4,IIf([CountOfInsp_Date]<DatePart("ww",date())*2,"Under",IIf([CountOfInsp_Date]=<DatePart("ww",date())*2.5,"On Target", "Over")))

[pbaldy]

You have an extra opening parentheses before the second IIf(). I think you just needed to flip that one around, but now it should be deleted. You also start 4 IIf() functions but only appear to close 3.

[Huddle]

It's still giving the error

=IIf(IsNull([Space]),IIf([FSL]=4,IIf([CountOfInsp_Date]<DatePart("ww",date())*2,"Under",IIf([CountOfInsp_Date]=<DatePart("ww",date())*2.5,"On Target","Over"))))

[pbaldy]

I don't think you ever provide a False argument for the first IIf (or the second now that I think about it).

[Huddle]

I broke it down to try to get the details right but I'm still missing something. Please see the attached.

[pbaldy]

You've added some new conditions to the mix? Personally, this is getting too complicated to do with a nested formula like this. I'd create a public function that accepts Space, FSL and the count as inputs and returns the desired value. It will be much easier to create the logic there, and easier to update if (when?) the logic changes in the future.