Query Date Range within a date Range

[Mick99]

Hi All,

I have a number of actions with start and finish dates. The issue is that I want to view a certain period within these dates and I cant seem to figure out how to do this. I have a form that allows to user to select the particular week they need to view, however the query only returns records that have dates in the range and don't allow for targeting dates in the range inside the range, if that makes sense.

 

[theDBguy]

Hi. Have you tried maybe something like this? Just curious...

...WHERE StartDateField<=[RangeEnd] And EndDateField>=[RangeStart]

(untested)

 

[Mick99]

No I havn't would that work in a query, I'll give it a go.

Cheers Mick

 

[Mick99]

No wouldn't work, thanks anyways

 

[arnelgp]

you need to use the Form's control on your Query:

select * from yourTable Where dateField between [Forms]![yourFormName]![startDateControl] And [Forms]![yourFormName]![endDateControl]

 

[theDBguy]

No wouldn't work, thanks anyways

Sorry to hear that. Can you show us how you tried to use it? Thanks.

 

[Mick99]

Hi [U]theDBguy[/U] , this seems to have done the trick. Thankyou!!!

RESULTS_FILTER: [WP_Activity_Start]<[Forms]![frm_Report_Selection]![Week_End] And [WP_Activity_Finish]>[Forms]![frm_Report_Selection]![Week_Start]

 

[theDBguy]

Hi [U]theDBguy[/U] , this seems to have done the trick. Thankyou!!!

RESULTS_FILTER: [WP_Activity_Start]<[Forms]![frm_Report_Selection]![Week_End] And [WP_Activity_Finish]>[Forms]![frm_Report_Selection]![Week_Start]

Oh, was it a false alarm then? Glad to hear you got it sorted out. Good luck with your project.