efficient method of finding closest date by specify input value

can it be specify the input value to the formate with YY-YY, that means let
the use to key in 09-10 from the query. and the 09-10 means the date range
(01/04/2009 to 31/03/2010)

staff
peter 01/08/2009
SAM 02/03/2009
IAN 03/01/2010
================== key in: 09-10 then, the output will shown below:
output:
peter 01/08/2009
IAN 03/01/2010

In query design view, enter something like this (as one line) into the
Criteria row under your date field:

= DateSerial([From 1 April what year?],4,1) And
DateSerial([From 1 April what year?]+1,4,1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"xmi" wrote in message
...
can it be specify the input value to the formate with YY-YY, that means
let
the use to key in 09-10 from the query. and the 09-10 means the date range
(01/04/2009 to 31/03/2010)

staff
peter 01/08/2009
SAM 02/03/2009
IAN 03/01/2010
================== key in: 09-10 then, the output will shown below:
output:
peter 01/08/2009
IAN 03/01/2010

Allen,
Your criteria will include SAM 02/03/2009 but it appears they want the
outer most dates.
What I do not get is their input criteria of 09-10 means the date
range (01/04/2009 to 31/03/2010).



--
Build a little, test a little.


"Allen Browne" wrote:

In query design view, enter something like this (as one line) into the
Criteria row under your date field:

= DateSerial([From 1 April what year?],4,1) And
DateSerial([From 1 April what year?]+1,4,1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"xmi" wrote in message
...
can it be specify the input value to the formate with YY-YY, that means
let
the use to key in 09-10 from the query. and the 09-10 means the date range
(01/04/2009 to 31/03/2010)

staff
peter 01/08/2009
SAM 02/03/2009
IAN 03/01/2010
================== key in: 09-10 then, the output will shown below:
output:
peter 01/08/2009
IAN 03/01/2010

.

This will give the outer most dates --
SELECT Aaron.SalesDate
FROM Aaron
WHERE Aaron.SalesDate =(SELECT TOP 1 [XX].[SalesDate] FROM Aaron AS [XX]
WHERE [XX].[SalesDate] cvdate([bottom date]) ORDER BY [XX].[SalesDate]) OR
Aaron.SalesDate =(SELECT TOP 1 [XX].[SalesDate] FROM Aaron AS [XX] WHERE
[XX].[SalesDate] cvdate([Top date]) ORDER BY [XX].[SalesDate] DESC);

--
Build a little, test a little.


"KARL DEWEY" wrote:

Allen,
Your criteria will include SAM 02/03/2009 but it appears they want the
outer most dates.
What I do not get is their input criteria of 09-10 means the date
range (01/04/2009 to 31/03/2010).



--
Build a little, test a little.


"Allen Browne" wrote:

In query design view, enter something like this (as one line) into the
Criteria row under your date field:

= DateSerial([From 1 April what year?],4,1) And
DateSerial([From 1 April what year?]+1,4,1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"xmi" wrote in message
...
can it be specify the input value to the formate with YY-YY, that means
let
the use to key in 09-10 from the query. and the 09-10 means the date range
(01/04/2009 to 31/03/2010)

staff
peter 01/08/2009
SAM 02/03/2009
IAN 03/01/2010
================== key in: 09-10 then, the output will shown below:
output:
peter 01/08/2009
IAN 03/01/2010

.