Data mismatch error result in query

[AndyDandy]

Hello,
Searching did not yield a prior forum posting so here is my dilema working with an Access query.

Filtering field: SURVCATEGORY (text field): the field could have a combination of letters or numbers (IA2, 2AI, I2, E1, ABCDE, ...)
Filter: Like "*I*" Or Like "*E*" Or Like "*1*" Or Like "*2*" And Not Like "*D*"
This filter is successful in extracting the data set desired.

I now wanted to identify the record with a calculated column that if the condition was met, the value of the exit date would display, otherwise "No" so I added LicYes: LicYes: IIf([survcateg] Like "*I*",[Exit_Date],IIf([survcateg] Like "*E*",[Exit_Date],"No"))
Comment: this results in a data type mismatch.

1. If I take Or Like "*1*" Or Like "*2*" out of my SURVCATEGORY filter LicYes works fine and there are no errors. Obviously it is not liking the numerals in this calculation.
2. I have tried using cstr() without success: LicYes: IIf(cstr([survcateg]) Like "*I*",[Exit_Date],IIf(cstr([survcateg]) Like "*E*",[Exit_Date],"No"))
3. I have tried using a calculated string of the SURVCATEGORY called STRCATEG using the CSTR([SURVCATEGORY]) formula without success in the LicYes field: IIf([STRCATEG] Like "*I ....... Still a data mismatch

I am missing something obvious here that for some reason I am missing. Any help appreciated

[rpeare]

you can't use a date and a text value in the same field. that's why you're getting a data mismatch. If you want it to *appear* as a date or a 'no' then you'll need something like:

LicYes: IIf([survcateg] Like "*I*",cstr([Exit_Date]),IIf([survcateg] Like "*E*",cstr([Exit_Date]),"No"))

where you are explicitly changing the date field to a string. Just bear in mind you will not be able to (in most cases) perform any sort of operation on this field treating it as a date.

[AndyDandy]

Thanks rpeare, that solved the data type mismatch. What confuses me though is that if I only filtered like "I" and "E" survcateg records then the LicYes calculation displaying the [exitdate] and "No" would work and there would be no data mismatch error. Knowing what to change though is more satisfying than knowing the answer to this confusion.

[rpeare]

It really depends on what your data looks like. Because you were not explicitly changing your dates so they appeared as strings it is possible your individual datasets may run fine, it's when it actually has to mix dates and text in the same column where the problem occurs. So for instance if you had a version of this query where *all* the results either had or didn't have an exit date, there would be no problem.

p.s. mark it solved if it's solved please!