Data type mismatch with a calculated column 1

[timoteo]

I am trying to run a query that returns individuals born after 1/1/1994. I am working with a legacy database and the birth date is stored as text. I was able to convert the records to date format using CDate. However, when I include a search parameter for people born after 1/1/1994 I get a data type mismatch error. I get the same error when I try to sort the "Reformatted Birth Date" column.

SELECT PatientData.PatientName, PatientData.SocSecNum, PatientData.BirthDate, PatientData.PatIDNum, LinkFormPat.TripSeqNum, CDate(Nz([BirthDate],"11/11/1911")) AS [Reformated BirthDate]
FROM PatientData INNER JOIN LinkFormPat ON PatientData.PatIDNum = LinkFormPat.PatIDNum
WHERE (((CDate(Nz([BirthDate],"11/11/1911")))>=#1/1/1994#));

Looking at the code I think the problem is the WHERE clause is using [BirthDate] for the search parameter. That field is still formatted as text. Is there a way to query the calculated column [Reformated BirthDate]created by the query?

Thank you in advance for any help.

 

[JonFer]

I would guess that some non-Null BirthDates are not being successfully converted to dates. Run a select with the Original and reformatted BirthDate included but no Where criteria and drop into Excel to sort.

 

[timoteo]

JonFer,

Thank you for the advice, you were right on. When I got the results into Excel I found three Birth Dates with garbage data in them. Once I corrected them, the query ran without a hitch.

Thanks again