Date Query

[Sandra P]

I have a start date field and an end date field which are service dates.
What I want to be able to do is a query that says I want all records returned
for any client that has active service between 1/1/06 and 6/30/06 (for
example). Using that example, if my start date was 9/1/05 and my end date
was 2/28/06, then a record would be returned...or if my start date was 5/1/06
and my end date was 9/30/06, a record would be returned...or if my start date
was 12/31/05 and my end date was 1/31/06, a record would be returned.

The way I have it written right now is this:

In the start date column (in the query) I have >= start date
In the end date column (in the query) I have <= end date

If I put in the criteria from above, 1/1/06 for the start date and 6/30/06
for the end date, it would only return records that were between 1/1/06 and
6/30/06.

Any ideas?

 

[Brian.M.Waters1]

Hey Sandra,

I think you're making it harder than it needs to be. For both columns
in your query you should have the same condition. They should have the
exact same condition "OR"'d together. So logically it would look like
"1/1/06<StartDate<6/30/06 OR 1/1/06<EndDate<6/30/06". Does this help?

B

 

[KARL DEWEY]

Create a table named CountNumber with a field named [CountNUM] that has
integers 0 through your maximum date span.

Use the two queries below and replace table William with your table and use
your field names instead of Primary, Field1, and Field2. Substitute your
date fields for [Start dt] and [End dt].

SELECT William.[Primary], DateAdd("d",[CountNUM],[Start dt]) AS [Month of
stay]
FROM William, CountNumber
WHERE (((DateAdd("d",[CountNUM],[Start dt])) Between [Enter beginning date]
And [Enter ending date] And (DateAdd("d",[CountNUM],[Start dt]))<=[End dt]))
ORDER BY William.[Primary];

SELECT William.[Primary], William.[Field1], William.[Field2]
FROM [Dates Between] INNER JOIN William ON [Dates Between].[Primary] =
William.[Primary]
GROUP BY William.[Primary], William.[Field1], William.[Field2];

 

[John Spencer]

Field: StartDate
Criteria: <=#6/30/06#

Field: EndDate
Criteria: >=#/1/1/06#

In words, you want any period where the Period start date was on or before
the target End Date and where the period EndDate was on or after the target
start date.

 

[Sandra P]

John

You hit the nail on the head. Thank you SO much. I have been struggling
with this and no one seemed to have the answer and it was so simple. I would
have never figured that one out!

Sandra