Conditional formatting and If statements in user form

[cardonas]

Hello everybody,

I am trying to do an IIF statement in my user form in access I have a field [Job Status] and I have a [Termdate] field I want a "String of text" to pop up in my [Jobstatuts] field base off of what the [Termdate] field has.


Heres my IIF statement

=IIf(([Term Date]=" " Or [Term Date] Is Null),"Active","Inactive")

Basically, If there is NO term date I want "ACTIVE" to pop up in my [Jobstatus] field and if there is a term date I want "Inactive" to populate in my [Jobstatus] field. If possible can have the text "Active" in GREEN and "Inactive" in red

I am not sure if I am putting the expression in the correct place. Also the user form is link to a back end table in access. I can not put the expression in the CONTROL SOURCE property since its link to the back end table.

 

[pbaldy]

How about

=IIf(IsDate([Term Date]),"Inactive","Active")

You can use Conditional Formatting to get the colors.

 

[cardonas]

My issue is I am not sure where to put the IIF statement on my user form ??

 

[pbaldy]

I'd have it as the control source of a textbox, because I wouldn't try to save a calculated value that changes all the time. If you insist:

http://allenbrowne.com/casu-14.html

 

[cardonas]

Thank you thats where I had it but then the field does not feed into the backend table in access.

 

[pbaldy]

Like I said, I wouldn't save it. If you want to, the link shows 2 ways.

 

[cardonas]

Just a quick update I used VBA instead and drop the code in the Afterupdate event in the [termdate] field below is my code in case anyone has the same issue I did and I used Conditional formatting to change the color

Private Sub Term_Date_AfterUpdate()
If Me.Term_Date = " " Then
  Me.Job_Status = "Active"
ElseIf IsNull(Me.Term_Date) Then
  Me.Job_Status = "Active"
Else
    Me.Job_Status = "Inactive"
    End If
End Sub

THANKS TO THIS FORUM LOVE THIS PAGE!!

 

[pbaldy]

Glad you got it sorted. I would have simplified with IsDate().

 

[cardonas]

Ah, thanks not sure what that is since I am a newbie but thanks any shortcuts are much appreciated.

 

[pbaldy]

Like:

If IsDate(Me.Term_Date) Then
  Me.Job_Status = "Inactive"
Else
  Me.Job_Status = "Active"
End If

 

[cardonas]

Thanks !! for your help and that is alot cleaner then mine

 

[pbaldy]

Happy to help!