By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,968 Members | 1,659 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,968 IT Pros & Developers. It's quick & easy.

year() in a where string

Expert 100+
P: 1,240
This one's got me talking to myself, and not in a good way.

Why does this query
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT Patients_eMD_qry.[eMD Patient], Year([born]) AS BirthYr
  2. FROM Patients_eMD_qry
  3. WHERE (((Year([born]))=Year([born])))
fail on error "Data Type Mismatch in Criteria Expression"?!!
Of course I don't really want to test Year(born) against itself. I was testing against the year of a text box entry and various tests led me to this. This just looked like the best way to highlight the absurdity of the situation.

Got similar error result with datediff(). If I remove the criteria, I see the birth years range from 1900 to 1998. I can even test for a literal year, i.e. 1928, and it still fails.

Using Access 2007, all service packs installed.

Jim
Feb 11 '15 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,430
Is born a date data type? If it's a string, convert it first using cdate()
Feb 11 '15 #2

Post your reply

Sign in to post your reply or Sign up for a free account.