Using a DLookUp in a date range criteria

J

JohnB

Hi. I'm selecting records using a date range criteria in a query and I want
the Year part to based on a field in a table. So if the value of
PlacementStartYear in tblSECPlacementStartYear is currently 2004, I want the
criteria to select all values that fall between 1st Sept 2004 and 31st Aug
2005.

I know the syntax of the following is all wrong but hopefully you can see
what I'm trying to do. Can anyone help with this please? Thanks, JohnB
 
R

Rick Brandt

JohnB said:
Hi. I'm selecting records using a date range criteria in a query and
I want the Year part to based on a field in a table. So if the value
of PlacementStartYear in tblSECPlacementStartYear is currently 2004,
I want the criteria to select all values that fall between 1st Sept
2004 and 31st Aug 2005.

I know the syntax of the following is all wrong but hopefully you can
see what I'm trying to do. Can anyone help with this please? Thanks,
JohnB
Click to expand...

BETWEEN
DateSerial(DLookUp("[PlacementStartYear]","[tblSECPlacementStartYear]"), 9,
1)
AND DateSerial(DLookUp("[PlacementStartYear]","[tblPlacementStartYear]") +
1, 8, 31)
 
J

JohnB

Thanks for the quick response, Rick. That works perfectly. Cheers, JohnB

Rick Brandt said:
JohnB said:
Hi. I'm selecting records using a date range criteria in a query and
I want the Year part to based on a field in a table. So if the value
of PlacementStartYear in tblSECPlacementStartYear is currently 2004,
I want the criteria to select all values that fall between 1st Sept
2004 and 31st Aug 2005.

I know the syntax of the following is all wrong but hopefully you can
see what I'm trying to do. Can anyone help with this please? Thanks,
JohnB
Click to expand...

BETWEEN
DateSerial(DLookUp("[PlacementStartYear]","[tblSECPlacementStartYear]"), 9,
1)
AND DateSerial(DLookUp("[PlacementStartYear]","[tblPlacementStartYear]") +
1, 8, 31)
Click to expand...
 
Top