iif statement not working

Question

hello

i have a form (form1) with two fields a yesno tick box called (yesno)  and a date field called date

what i would like is if the yesno box is ticked then the date field fill its self in with todays date!?!? i have used this formula =IIf([yesno].[Value]="yes",[date]=Date(),[date]="") but to no avail

any help would be great!

Answer 1

Another point to make here is that Date is a Reserved word in Access and shouldn't be used for an object name. You should use better naming, you want the name of your field to describe the information stored in it, not the type of data.

---

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2009 Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

Answer 2

To correct your IIF statement you have to understand that Checkboxes operate as True/False which also corresponds to -1/0:

=IIf([yesno]=True, Date(), "")

But that said, the proper approach would be to use the afterupdate event of your Checkbox to populate the date control with the current date.  Also, you should not label/name a control date as it is a reserved word and will lead to a problem.  Name it RegistrationDate, FillinDate,... something a little more descriptive.  Then in the afterupdate event of your Checkbox you'd do somthing like

If IsNull(Me.MyDateControlName)=True Then

   Me.MyDateControlName = Date()

End If

I hope that helps,

Daniel Pineault

http://www.cardaconsultants.com

MS Access Tips and Code Samples: http://www.devhut.net

Answer 3

Try:

    =IIf([yesno], Date(), Null)

There were several issues with your expression:

• .Value is not needed
• The Value of a yes/no field is True or False, which is not the same as the text "yes".
• You can't assign a value to some other control (named "date"?) like that.
• You need to use Null, not a zero-length string as the alternate value. Otherwise Access will treat it as a string and not as a date value.

---

Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to forum, rather than allenbrowne at mvps dot org.