If then statement in form using two separate fields

[heavenlyhost1]

I have a yes/no field called HCMS Forwarded. If this field is checked, then
I need another field to populate with the date the HCMS field was checked
plus 10 additional days. I am currently using IIF([HCMS
Forwarded]=False,NOW()+10,"") in the Default Value of [FOLLOW_UP]. It was
working, but would not let the user update a prior record with checkmark and
was also populating all the records with the date. Now it's populating the
field with 12/29/1989 for some reason. HELP!!!

 

[Allen Browne]

The problem is with the zero-length string, which cannot apply to a date.
This forces Access to treat the value as text. You need to use Null instead.

Unless you want the time as well as the date, you need Date() rather than
now. So, the expression might be:
=IIf([HCMS Forwarded]=False, Date() + 10, Null)

Note that this won't work in the Default Value property. The Default Value
is assigned before you start entering anything, so it won't respond to
checking the box. You need to use the AfterUpdate event of the check box to
respond to the change.

If changing the check box affects all rows, it is unbound. You need to set
its Control Source property to the name of the yes/no field.

This article provides more info about working with calculated fields:
http://allenbrowne.com/casu-14.html

 

[Linq Adams via AccessMonster.com]

I'm not at all sure how this code ever worked. You say

"If this field is checked, then I need another field to populate with the
date the HCMS field was checked plus 10 additional days."

yet in your code

IIF([HCMSForwarded]=False,NOW()+10,"")

says if HCMSForwarded is ***not checked*** (= False) then populate Follow_Up
with the date plus ten days. One is the exact opposite of the other.

I'd use the AfterUpdate event of the checkbox for this:

Private Sub HCMSForwarded_AfterUpdate()
If HCMSForwarded Then
Me.Follow_Up = DateAdd("d", 10, Date)
Else
Me.Follow_Up = Null
End If
End Sub

The Else clause is necessary in case the user accidently/erroneously ticks
the checkbox and then unticks it.