Data type mismatch in criteria expression

[WhiZa]

I'm getting the error:

Data type mismatch in criteria expression

on this statement:

SELECT Name, Year(Birthdate)
FROM People
WHERE Year(Birthdate) = 1977

The field brithdate is of type Text. When I remove the criteri
expression I get the correct years for every persons birthdate. Wh
would it give me the error for the given criteria

 

[Van T. Dinh]

You are relying on automatic type-casting of BirthDate (Text) to DateTime
which JET may have problems interpreting.

Try:

.... WHERE Year(CDate([Birthdate])) = 1977

 

[Allen Browne]

The Year() function expects a numeric value (a date), not a text value.

The best solution is to replace the text field with a real date field.

As a workaround, you may be able to use this if the text field is correctly
formed for your locale:
Year(CDate(BirthDate))