I have a text field field in a table. The data in this field will either have text or a date in dd/mm/yyyy format.

I want to write an IIf statement like

Status: IIf(CDate([RescheduledTCI])<Date(),"Seen within 28 Day","Still Waiting for TCI")

where I have text in [RescheduledTCI] I get #Error instead of "Still Waiting for TCI". Can anyone help?

If [RescheduledTCI] does not contain a string that is capable of being resolved as a date then CDate() will give an error. So it's not the Iif statement that is giving the error. You will have to change your logic in one way or another. Mixing dates and text in the same field is asking for complications. If you need this functionality, then it would be better to use two fields, one date/time and one text.

Try

IIf(IIF(IsDate([RescheduledTCI]),(CDate([RescheduledTCI])<Date(),"Seen within 28 Day","Still Waiting for TCI"),"Not a date")

CodeMaster::cool:

I'll try that and repost if I still have a problem. Thanks.

Neil, I understand what you are saying, unfortunately the problem was that it was a development made by someone else. I have to pick up the pieces when something doesn't work and often have to live with structure of the table and it's contents.

Understood. Been there, done that!