If then statement in form using two separate fields

  • Thread starter Thread starter heavenlyhost1
  • Start date Start date
H

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!!!:)
 
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
 
Back
Top