Using a DLookUp in a date range criteria

G

Guest

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)
 
G

Guest

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Academic Year date criteria in a query 7
Repost.. Using Just Yearear in criteria for Long Date Field 2
Date Calculation in a query 1
Date Criteria 4
Date Criteria Range 1
Update query with date criteria 3
show criteria data from a query on a report using that query 2
Date Range 2

Top