Combining 3 textboxes

[Dallass Drake]

I need a text box in a form to display a series of error messages depending on the result of a DateDiff result, but I'm having trouble creating a single formula that encapsulates all error messages, but I have been able to get this working easily enough using 3 text boxes (For 3 seperate error messages one for results between 7 and 13 days, another for between 14 and 27 days and a third for 28+ days) and the same basic formula, I just need help combining them into a single formula for a single text box. I'm using these:

=IIf([DateDiff]>=7<14 and [Bleh] is Null, "7 Day Warning", "")
=IIf([DateDiff]>=14<27 and [Bleh] is Null, "14 Day Warning", "")
=IIf([DateDiff]>=28 and [Bleh] is Null, "28 Day Warning", "")

How would one go about combining all those formula's? I tried for ages today but I didn't come up with much, experimenting with Or IIf etc... but coming up with -1's all the time. Any help would be appreciated

 

[DALeffler]

Assuming you are not trying to call the DateDiff function in the IIF statement, while you can combine it into one statement:

=IIf(IsNull([Bleh]),IIf([fldDateDiff]<7,"",IIf([fldDateDiff]<14,"7 Day Warning",IIf([fldDateDiff]<27,"14 Day Warning","28 Day Warning"))))

I think a better solution is to call a function:

=fncMyFunction([Bleh],[fldDateDiff])

Function MyFunction (strBleh as String, lngDateDiff as Long) as String

MyFunction = ""

If IsNull(strBleh] Then
     Select Case lngDateDiff
          Case < 7
                Exit Function
          Case < 14
                MyFunction = "7 Day Warning"
                Exit Function
          Case < 28
                MyFunction = "14 Day Warning"
                Exit Function
          Case Else
                MyFunction = "28 Day Warning"
     End Select
End If

End Function

 

[Dallass Drake]

Thanks, the IIf formula proved very helpful and now I have everything up and running a treat