Access 2013: I am trying to return today's date when a yes/no fields is marked as "Yes"

I am trying to return today's date in a date field when another field is marked as "Yes". I tried the following expression but to no avail. I have limited experience with expressions and greatly appreciate any guidance. If the field does not have a "Yes" the date field can be blank. I cannot set the default to "No" for the Approved field.

Approved Date: IIf([Approved]=True,Today(),Null)

If I set the date, it will work but the date is dynamic so this is not really helpful. Approved Date: IIf([Approved]=True,5/1/2016,Null)

Thank you in advance for your help.

Solution

OK, so I spent a little time looking at how to do this with an expression. The answer is you can't, at least not without a helper function. So, my hybrid solution is:

1) Create a function in a standard module:

Public Function SetControlValue( _
    ByVal ctlControl As Access.Control, _
    ByVal varValue As Variant, _
    Optional ByVal varTest As Variant = True)
        If (varTest) Then
            ctlControl.Value = varValue
        End If
End Function

2) In the AfterUpdate event for the Approved checkbox, enter:

=SetControlValue([ApprovedDate],Date(),([Approved]=True) AND (IsNull([ApprovedDate])))

This approach saves you from making a class module under the form. And you can keep all such code in a common module, so you can build a library of such functions for other forms.

(old answer 2)

Based on your answers, what you can do is add an AfterUpdate event to the Approved control, which does something like:

Private Sub Approved_AfterUpdate() 
    If (Approved.Value = True) And IsNull(ApprovedDate.Value) Then 
        ApprovedDate.Value = Date()
    End If
End Sub

This will set the approved date once, when the approved checkbox is first checked. If you need different behavior, this can easily be modified.

(old answer 1)

I think you have it backwards. When [Approved] is set to True, set [ApprovedDate] = Today(). That way, it is saved to the table, and you have a permanent record of when it was approved.