Another string to date conversion problem

[grnzbra]

I have a table with a text field to receive dates or "na" or "n/a". From this, I am trying to run a query that selects all dates that are not "na" and not "n/a" and not null. This works fine with a criteria of

"Is Not Null And Not In ("NA","N/A")"

The problem is, I also must select a date range. In the query, I have created a calculated field equal to various date conversion functions and they all seem to work at converting the text to date. However, I get a Type Mismatch error any time I try to do anything to the date field such as select a date range or to sort on it.

Any suggestions?

 

[ejstefl]

Trying using the IsDate function.

Iif(IsDate([DateField]), cDate([DateField]), Null)

 

[gemma-the-husky]

You obviously cant store "n/a" in a date field, or it becomes a text field. Why dont you store the date as a date, and store an additional yes/no field to determine whether a blank is missing data or is a true "n/a". Then you can search the date field as a date field, avoiding all the (slow) text functions you are having to do at the moment. Alternatively store the datevalue(0) to represent the n/a value, as opposed to a null date. 0 resolves as a 1899 date - i forget which.

 

[grnzbra]

Unfortunately, this was designed by people who were accountants who knew a little about Access. I'm stuck with what exists and have to make it work. I've even tried loading into a table where the date is a date/time field and got my wrists slapped, "NO LOCAL TABLES!".

I'll try the IIf solution.

Thanks for the help

 

[grnzbra]

The IIf solution works