Match year part of a date

Following on from an earlier enquiry, is it possible to match part of a date field in an SQL query?

EG: If I have lngYear = 2003, can I have:

strSQL = SELECT tblTable.* WHERE (the year part of)tblTable.Date = " & lngYear & ";"

??

Failing that, it wouldn't be much trouble for me to use (eg) 1/1/2003 (instead of simply 2003).

I would then need to SELECT WHERE tblTable.Date was between 1/1/2003 and 1/1/2003 plus one year, which is something else I don't know how to do!

Ideally I'd like to use the first option though, if possible.

Ta

actually

strSQL = "SELECT * WHERE SELECT DATEPART( year, [Date] ) = " & lngYear

Also note, its baaaaaaaaaaaaaaaaad practice to use reserved words as column names, you are just asking for trouble.... in the above example, there's [ ]'s to combat this, but its still bad practice

Edited by MorningZ

Hmm. Still got a problem.

Here's the code (not the full statement I want to use, but an initial, simple one, to test the theory):

strSQL = "SELECT tblDates.Start_Date FROM tblDates WHERE SELECT DATEPART(year,tblDates.Start_Date) = " & lngYear & ";"

And here's the error...

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error. in query expression 'SELECT DATEPART(year,tblDates.Start_Date) = 2003'.

I'm guessing the syntax bit is the second use of "SELECT". Taking it out gives, instead:

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

lngYear exists (I've response.writen the strSQL and it shows as strSQL = SELECT tblDates.Start_Date FROM tblDates WHERE DATEPART(year,tblDates.Start_Date) = 2003;

Any more ideas?

Ta

No, I don't understand why it's not working either! I have replace the lngYear bit with a number as per your example (tried 2002 and 2003 which are both present in strat_Date) and it's still throwing a wobbly.

Going to take a break, restart, come back, kick it, and try agin...