iif and date format problem

[ArvinFx]

In my report,I need to set the dates before 1/1/2017 as "Expire" instead of ?/?/2017, and after that time the filed date but in dd-mmm-yy format.



for this reason i have to write a query and set an "IIF" function as you can see in attachment, but problem is output of IIF function. It is text it is not Date! so Msaccess can not sort it or do any format style on it! I searched and found CDate function and half of my problem solved but when this function input is "Expire" the output is #Error !!!

Plz show me the way. thanks

[ranman256]

Make 2 fields,1 text says EXPIRED, the other is the date.
make 1 visible,one not.

txtExpire.visible=txtDate<="1/1/17"
txtDate.visible = not txtExpire.visible

[ArvinFx]

Thanks, Where I have to put this code in Report form?

[aytee111]

In your query, remove the CDate in the IIf statement - you are trying to change the word "Expire" into a date and Access doesn't like that.

Not sure what you are wanting with the sort order.