using Datepart in an SQL query

[dogdays]

win 7 32bit, access 2003
I have two select statements involving Datepart, the first works, the second returns nothing. Advice appreciated
Working select

SELECT DISTINCT Datepart('yyyy',dtDateEntered) FROM tblFiscalGifts WHERE Datepart('yyyy',dtDateEntered) >= 2001 ORDER BY Datepart('yyyy',dtDateEntered)

Non working select

INSERT INTO tblTemplatePreviousDonorsj SELECT DISTINCT nHouseholdNumber, nIndividualNumber FROM tblFiscalGifts WHERE Datepart('yyyy',dtDateEntered) IN (2001,2002,2003,2004,2005,2006,2007,2008,2009)

tia Jack

 

[gaj104]

INSERT INTO tblTemplatePreviousDonorsj

Note the 2nd statement is an insert statement not a select statement per se.

If you just look at the select statement within the insert the sql seems fine to me, based on dummy data I put together:

SELECT DISTINCT nHouseholdNumber, nIndividualNumber FROM tblFiscalGifts WHERE Datepart('yyyy',dtDateEntered) IN (2001,2002,2003,2004,2005,2006,2007,2008,2009)

Table

nHouseholdNumber nIndividualNumber dtDateEntered
1 1 31/12/2002
2 2 25/01/2001
3 3 25/01/1980
3 3 06/09/2010

Gives you:

nHouseholdNumber nIndividualNumber
1 1
2 2

(excuse formatting - need to get the addin)

 

[dogdays]

Graeme:
I did just the select part and you are correct, it does work. With that guidance I was able to solve the INSERT problem.

thx Jack

 

[boblarson]

By the way, instead of using DatePart, I would use YEAR([Field]) or MONTH([Field]) or DAY([Field]) when you need those parts.

 

[dogdays]

Bob:
What are the advantages to using your recommendation? It does look cleaner.

Jack

 

[boblarson]

Bob:
What are the advantages to using your recommendation? It does look cleaner.

Jack

I don't know that there is any advantage, other than it is less typing and easier to read when in with a whole lot of other SQL or VBA. There probably is an advantage to using DatePart if you need to be able to specify which day is to be considered the first day of the week and/or which week is to be considered the first week of the year (probably mostly for the week number calculation).