Search Within Dates

[philljp390]

Hi all,

I have a table with dates in:

Start Date______End Date
01/03/05_______10/03/05

I have a form where you type in the start and end dates and when you click the search button it runs a query to display all record that have dates between those typed.

The problem i have, is if i were to search for:

Start Date = 02/03/05
End Date = 07/03/05

It would not display the record in bold above, even though it has dates within those in the search criteria. In the query i have the following:

Start Date >=([Forms]![FrmSearch]![TxtStart])

End Date <=([Forms]![FrmSearch]![TxtEnd])

So how do i get the search to recognise these as dates and look for the days between the dates in the table.

Please help

 

[Jon K]

Switch the criteria, i.e.

[Start Date] <=[Forms]![FrmSearch]![TxtEnd]

[End Date] >=[Forms]![FrmSearch]![TxtStart]
.

 

[philljp390]

Switch the criteria, i.e.

[Start Date] <=[Forms]![FrmSearch]![TxtEnd]

[End Date] >=[Forms]![FrmSearch]![TxtStart]
.

But then what if you did a search for:

Start Date = 25/02/05
End Date = 15/03/05

Then the record:

Start Date______End Date
01/03/05_______10/03/05

Would not show up.

Please help.

 

[john471]

Start Date >=([Forms]![FrmSearch]![TxtStart])
End Date <=([Forms]![FrmSearch]![TxtEnd])

Are you combining these as "AND" or "OR" ?

I think you may want something along the lines of....

(I don't have any time to check the syntax or the theory, or anything kind like that - so here it is straight form thought to keyboard )

"
Start Date BETWEEN ([Forms]![FrmSearch]![TxtStart]) AND ([Forms]![FrmSearch]![TxtEnd])

OR

End Date BETWEEN ([Forms]![FrmSearch]![TxtStart]) AND
([Forms]![FrmSearch]![TxtEnd])
"

i.e. select records where the StartDate is between the user-input specified start and end dates OR the EndDate is between the user-input specified start and end dates.

This should give any record that encroaches on the specified dates. Change the "OR" to "AND" to only get records that are wholly contained in the specified dates.

P.S. Also having trouble working out if you mean the First of March, or the Third of January.... so having a little trouble getting my head around your example

 

[philljp390]

Are you combining these as "AND" or "OR" ?

I think you may want something along the lines of....

(I don't have any time to check the syntax or the theory, or anything kind like that - so here it is straight form thought to keyboard )

"
Start Date BETWEEN ([Forms]![FrmSearch]![TxtStart]) AND ([Forms]![FrmSearch]![TxtEnd])

OR

End Date BETWEEN ([Forms]![FrmSearch]![TxtStart]) AND
([Forms]![FrmSearch]![TxtEnd])
"

i.e. select records where the StartDate is between the user-input specified start and end dates OR the EndDate is between the user-input specified start and end dates.

This should give any record that encroaches on the specified dates. Change the "OR" to "AND" to only get records that are wholly contained in the specified dates.

P.S. Also having trouble working out if you mean the First of March, or the Third of January.... so having a little trouble getting my head around your example

Nice One!!!

Ive changed it a bit:

Start Date Between ([Forms]![FrmSearch]![TxtStart]) And ([Forms]![FrmSearch]![TxtEnd]) Or ( Forms![FrmSearch]![TxtStart] Between [Holidays]![Holiday Start Date 2] And [Holidays]![Holiday Finish Date 2])


End Date Between ([Forms]![FrmSearch]![TxtStart]) And ([Forms]![FrmSearch]![TxtEnd]) Or ([Forms]![FrmSearch]![TxtEnd] Between [Holidays]![Holiday Start Date 2] And [Holidays]![Holiday Finish Date 2])

Testing is looking like this is working now.

Thanks again!

 

[EMP]

Originally posted by philljp390

But then what if you did a search for:

Start Date = 25/02/05
End Date = 15/03/05

Then the record:

Start Date______End Date
01/03/05_______10/03/05

Would not show up.[/b]

I tested Jon K's criteria.

Contrary to what you posted, the record 01/03/05_______10/03/05 showed up when I searched for

Start Date = 25/02/05
End Date = 15/03/05


And I added other records in the test. Jon's simply criteria was able to correctly return the records. Attached is the database I tested.

 

[philljp390]

I tested Jon K's criteria.

Contrary to what you posted, the record 01/03/05_______10/03/05 showed up when I searched for

Start Date = 25/02/05
End Date = 15/03/05


And I added other records in the test. Jon's simply criteria was able to correctly return the records. Attached is the database I tested.

I have tested your database and your right, it works. I dont understand it though, how can this be?!

If you entered to search a start date: 25/02/05 using the following:

Start Date Record <=[Forms]![FrmSearch]![TxtEnd]

Start Date Record = 01/03/05

How can it still display the result of 01/03/05. See, if we look at it like this:

01/03/05 <= 25/02/05

That isnt true, so how come it still displays this?!

 

[Jon K]

It works because

[Start Date] <=[Forms]![FrmSearch]![TxtEnd]
01/03/05 <= 15/03/05

[End Date] >=[Forms]![FrmSearch]![TxtStart]
10/03/05 >= 25/02/05
.

 

[philljp390]

It works because

[Start Date] <=[Forms]![FrmSearch]![TxtEnd]
01/03/05 <= 15/03/05

[End Date] >=[Forms]![FrmSearch]![TxtStart]
10/03/05 >= 25/02/05
.

yes, i understand what you have written. But im saying this:

20/03/05 <= 15/03/05

That still somehow comes out as true.

For example. The records in the table are 20/03/05 - 25/03/05, and i type to search for records between 15/03/05 - 30/03/05.

Do you see what I mean?

 

[Jon K]

20/03/05 - 25/03/05 falls within the search criteria of 15/03/05 - 30/03/05, so it must come out as true. It is what the query should do.


I believe the criteria for searching date ranges is a little tricky.
[Start Date] <=[Forms]![FrmSearch]![TxtEnd]
[End Date] >=[Forms]![FrmSearch]![TxtStart]

As in my ealier post where I asked you to switch the criteria,
it has to compare [Start Date] with [TxtEnd] and [End Date] with [TxtStart].


In 20/03/05 <= 15/03/05, you were comparing [Start Date] with [TxtStart], the result of which doesn't affect the record selection as [Start Date] <=[Forms]![FrmSearch]![TxtStart] is not a component part of the criteria.
.