Access 2007 Date Query Glitch

[MSAccessRookie]

Have you tried using BOTH of the Form Fields in each test? It appears that in your sample data, when the data ranges overlap the dates selected on the Form, the selection is ignored. There are also some others that are not so easily explained.

[FONT=Courier New]Range: 1929-1935[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]440 1927-1930  (1927 is outside the Range)
441 1931-1936  (1936 is outside the Range)
445 1928-1932  (1928 is outside the Range)
586 1930-1931  (no idea why this is not present)
427 1932-1934  (no idea why this is not present)
428 1934-1936  (1936 is outside the Range)[/FONT]

In the sample code below, if either the Start Date or the End Date is in the range, it will be selected. If this does not get you what you are looking for, you should be able to play with the AND and OR Statements until you get what you want.

(((Year([Start_Date])   >=[Forms]![SearchForm]![FormStart]) AND
 (Year([Start_Date])   <=[Forms]![SearchForm]![FormEnd])) OR
((Year([End_Date])   >=[Forms]![SearchForm]![FormStart]) AND
 (Year([End_Date])   <=[Forms]![SearchForm]![FormEnd])))

 

[spikepl]

To determine overlapping ranges use this: http://www.baldyweb.com/OverLap.htm .

 

[RFabert]

Hello MSAcessRookie,

Are you saying that you found instances where the solution suggested by Nigel doesn't work? Thank you for your efforts.

Also, could you (or anyone else) outline your testing strategy? I'd like to adopt a rigorous and reliable testing strategy for my queries.

Thank you!

 

[RFabert]

Hello Spikepl,

Thank you for the reference. It has been bookmarked!

 

[nanscombe]

The film start year is before (<=) the form end year
AND
The film end year is after (>=) the form start year

Start before or equal to end
End after or equal to start


From the BaldyWeb page

WHERE OffStartDate <= Forms!FormName.txtEndDate And OffEndDate >= Forms!FormName.txtStartDate

Start before or equal to end
End after or equal to start