Queries to retrieve records which fall between two dates

[yam]

Hi,


I have a table with fields "Approval Date" and "Expiry Date" for each record. I want to retrieve records which fall in a period. (eg. active in 2010).

Then I make a parameter query [EnterYear].

[EnterYear]>=Year([Approval Date]) And [EnterYear]<=Year([Expiry Date]).

It seems work for calendar year ( 1 Jan- 31 Dec).


Now I want to have records for financial year period ( eg. FY 2012 is from 1 April 2012 to 31 Mar 2013). How can I do query for this kind of period?


Example of Records in table:

Approval Date Expiry Date
Record(1) 1/1/2008 30/04/2009
Record(2) 10/2/2009 9/2/2010
Record(3) 1/1/2009 1/1/2012
Record(4) 1/1/2010 1/1/2014
Record(5) 1/1/2011 1/1/2012

If User look for Active record in 2010 Financial year ( 1 April 2010 to 30 Mar 2011), the result should be Record 3,4 and 5. Please advise how to make a query.

Regards,
YAM

[June7]

Calculate the fiscal year for date fields and use those values as you have for calendar year.

[Enter Date] BETWEEN Year(ApprovalDate) - IIf(Month(ApprovalDate)<4, 1, 0) AND Year(ExpiryDate) - IIf(Month(ExpiryDate)<4, 1, 0)

[yam]

Calculate the fiscal year for date fields and use those values as you have for calendar year.

[Enter Date] BETWEEN Year(ApprovalDate) - IIf(Month(ApprovalDate)<4, 1, 0) AND Year(ExpiryDate) - IIf(Month(ExpiryDate)<4, 1, 0)

Hi,

Thanks for reply. As Record(3) 1/1/2009, 1/1/2012 (Approval Date, Expiry Date) is still ongoing in FY 2010, it has to be in result too. I want to have all records which is ongoing in FY 2010.


Thanks for advise.

YAM

[June7]

Issue resolved?

[yam]

Issue resolved?

It is working . Thanks for help.