IIF condition for a date in a field on a form

[AshikHusein]

I always get bogged down with the date functions in Access.

I have a table with a field called Remark2_Date (a Date/Time field) which is bound to a field on the form. Now when there is a change to this field the entry should be updated to null if the date is 1/1/2001.

For the change event for the box on the form Remar2_Date using the "Expression Builder" I have added the following:

=IIf(Format([Remark2_Date],"dd/mm/yyyy")=1/1/2001,Null,[Remark2_Date])

But the above does not work. Would appreciate help with the above. Thanks.

 

[Mile-O]

=IIf(Format([Remark2_Date], "dd/mm/yyyy")=1/1/2001, Null, [Remark2_Date])

OKay, two things here:

• The Format function returns a string, not a date
• Date literals are enclosed within hash marks (#)

So, firstly, we need to put # on either side of the date literal. The expression becomes:

=IIf(Format([Remark2_Date], "dd/mm/yyyy")=#1/1/2001#, Null, [Remark2_Date])

If, for example, the date was 1/1/2001 then putting it through the Format function would return "01/01/2001".

So, the formula would look like this:

=IIf(Format("01/01/2001" = #1/1/2001#, Null, [Remark2_Date])

Now you can see that a string can't immediately equal a date, therefore the expression will more likely return an error. Because the field [Remark2_Date] will be a date field, it's safe to say that it can be compared with #1/1/2001#. Treating the date to a format function should be done on the relevant return value of the expression.

Ergo, your final formula should be:

=IIf([Remark2_Date] = #1/1/2001#, Null, Format([Remark2_Date], "dd/mm/yyyy"))

 

[AshikHusein]

I tried the iif statement you gave me bu it still does not work:

=IIf([Remark2_Date] = #1/1/2001#, Null, Format([Remark2_Date], "dd/mm/yyyy")).

When I put in the date as 01/01/2001 and then go back to check the field, it should be null. But it continues to show 1/1/2001.

This is being placed on the change event for "Remark2_Date". Thanks

 

[AshikHusein]

Thanks Pat!

That worked very well!