IIf function to change textbox properties

[Courtman]

I am trying to run an IIf function as follows:

=IIf([MedicalDueDate]>Date(),IIf(([MedicalDueDate]-Date())<15,"P","P"),"O")

So this will check to see if the medical is valid. If it is valid, it will show a green P (in wingdings 2 shows a tick). If it is valid, but within 2wks of expiry it will show an orange P, and if the medical is expired it will show a red cross.

The above IIf string works fine for entering the characters, but can I also change the textboxes FORECOLOR property at the same time? I've tried using AND, but this doesn't work - just errors.

If anyone knows an easier way of achieving this I'd be grateful...

 

[shkrebs]

Hi Courtman.

I don't have a final solution on this, but I would write a function where I'm running a Case on the result of the IIF and depending on the result I would change the property of the textbox.

Hope this is a help.

Regards
Soren

 

[Courtman]

Sounds like it might work. How would I write the case though? Would that be a validation rule?

 

[shkrebs]

Ehh, well a case is basically like this:

Select Case ZIPCODE
Case "28000" To "28099"
CITY = "Madrid"
Case "41000" To "41099"
CITY = "Sevilla"
End Select

Depending on how your users are typing in the data, I would use the AfterUpdate property of that field and then write the function in VBA.

Good luck.
Soren

 

[Courtman]

Thanks for the input. I've solved the problem now by doing the following:

If SingleCofRdue < date Then
singlecofrtick.ForeColor = 255
End If

If (SingleCofRdue - date) - 365 Then
singlecofrtick.ForeColor = 65535
Else
singlecofrtick.ForeColor = 32768
End If

And then running this code every time the window starts. It may not be pretty but it works...!