Multiiple IIFs Statement

[soldat452002]

Hello


My expressions are not working correctly. Some of the dates are not calculating correctly. Can someone let me know if im using my expressions correctly?

---field properties
[Status] (30 Day Notification, 15 Day Notification, 1 day Expired, 30 Day Expired)
[Date Created] Date stored as text

---This is my Expression to convert TEXT to Date for Date Created field.
DateCreated: DateValue([Cases].[Created On])

---Then get the Difference between Dates
SLA: DateDiff("d",[qry_Cases].[DateCreated],Now())


--- This expression is to look at many Statuses and give me the dates the cases have been opened and if out of compliance.
SLA_Status: IIf([qry_Cases].[Status]='30 Day Notification' And [SLA] <='16',"Within SLA",IIf([qry_Cases].[Status]='15 Day Notification' And [SLA] Between '14' And '31',"Within SLA",IIf([qry_Cases].[Status]='1 Day Expired Notification' And [SLA] Between '31' And '61',"Within SLA",IIf([qry_Cases].[Status]='30 Day Expired Notification' And [SLA] Between '62' And '80',"Within SLA","Out of SLA"))))

[JoeM]

One thing. Since DataDiff returns a numeric value (the number of days), remove the single quotes you have around the numbers. Single quotes are text qualifiers, so you would not use them around numeric values, i.e.

And [SLA] <=16,

Also, in your SLA calculation, note that Now() has a time component, which will lead to a fractional component in the SLA value that gets returned. If you do not want that, use Date() instead of Now().

[pbaldy]

Well for starters, the SLA calculation will return a numeric value, so the values in your IIf() should not be surrounded by quotes:

[SLA] <=16

[June7]

First you say [Date Created] is a text field storing value then you show [DateCreated] as a calculated value. One name has space, the other doesn't. Are these the same field?

SLA is a number, don't use apostrophe delimiters. Only one IIf needed.

SLA_Status: IIf(([Status]='30 Day Notification' And [SLA] <=16) Or ([Status]='15 Day Notification' And [SLA] Between 14 And 31) Or ([Status]='1 Day Expired Notification' And [SLA] Between 31 And 61) Or ([Status]='30 Day Expired Notification' And [SLA] Between 62 And 80), "Within SLA", "Out of SLA")

[soldat452002]

Thank you all! removing the double quotes fixed the expression.