Date Part Function?

[NeonSky via AccessMonster.com]

Hello!

I have a query and a table...

queryONE

FieldYear
2007
2008
2009

tableONE

FieldDate
1/1/2007
5/7/2008
5/9/2009
6/5/2010

I would like a query to only return the "FieldDate" values where the year
matches a value in "qryONE", not sure how to implement the function directly
into my query, hence the final dataset would return....

qryFINAL
FieldDate
1/1/2007
5/7/2008
5/9/2009

Thank you!

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use the Year() function in the JOIN's ON expression:

SELECT T.FieldDate
FROM queryOne As Q INNER JOIN tableOne As T ON Q.FieldYear =
Year(FieldDate)

You will not be able to view this in Design View.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSUsgFYechKqOuFEgEQJEGwCdHXo2fuhATJEQuZ72FsHpA1IYR8MAnikF
DPegOKiIgwTjcgiVuuvhAZ79
=Tcse
-----END PGP SIGNATURE-----

 

[John W. Vinson]

I would like a query to only return the "FieldDate" values where the year
matches a value in "qryONE", not sure how to implement the function directly
into my query, hence the final dataset would return....

MGFoster's suggestion will work, but if the table is very large it may be
inefficient; no solution is ideal, but a solution that will take advantage of
an index on the FieldDate would be

SELECT TableOne.FieldDate
INNER JOIN qryOne
ON TableOne.FieldDate >= DateSerial(qryOne.FieldYear, 1, 1)
AND TableOne.FieldDate < DateSerial(qryOne.FieldYear + 1, 1, 1)

 

[NeonSky via AccessMonster.com]

Thank you John and MGFoster, very helpful!

MGFoster's suggestion will work, but if the table is very large it may be
inefficient; no solution is ideal, but a solution that will take advantage of
an index on the FieldDate would be

SELECT TableOne.FieldDate
INNER JOIN qryOne
ON TableOne.FieldDate >= DateSerial(qryOne.FieldYear, 1, 1)
AND TableOne.FieldDate < DateSerial(qryOne.FieldYear + 1, 1, 1)