Simple Searching by TIME RANGE?

[vasnas]

Hello, im trying to do a simple search/filtering based on a time range. Each record has a StartTime and EndTime (Short Date field). I want the result of the search to be inclusive. ie. the searchable time range should be inside in the actual time range of a record.

i have tried several codes like:
(txt_StartTime etc are the unbound text boxes values in the search form...

Dim sql as String
If Me.txt_StartTime.Value <> "" AND Me.txt_EndTime.Value <> "" Then
sql = sql & " AND (StartTime >= " & Me.txt_StartTime.Value & ") AND (EndTime <= " & Me.txt_EndTime.Value & ")"

...ive tried it with #StartTime#...but doesnt work either...
Any suggestions for the code please? Thank you in advance for any help!! Vas

 

[PHV]

What about this ?
sql = sql & " AND StartTime<=#" & Me.txt_EndTime.Value & "# AND EndTime>=#" & Me.txt_StartTime.Value & "#"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vasnas]

Hello PHV, thank you for your reply, im afraid your suggestion did not work...

i also tried:
If Me.txt_TS.Value <> "" And Me.txt_TF.Value <> "" Then
sql = sql & "
AND (TS <= #" & Me.txt_TS.Value & "#
AND TS <= #" & Me.txt_TF.Value & "#
AND TF >= #" & Me.txt_TS.Value & "#
AND TF >= #" & Me.txt_TF.Value & "#)"
....and its not working...(i hope the use of #'s is correct)
TS=TimeStart
TF=TimeFinish

I remind you that the purpose is to find the 'Shops' that are open through out the Time range im searching for (no overlapping). Im sure its a simple code, but for a new Access user like me date/time fields are very difficult to program.
Any other suggestions?!
Thanks again!

 

[PHV]

And this ?
sql = sql & " AND StartTime>=#" & Format(Me!txt_StartTime, 'yyyy-mm-dd') & "# AND EndTime<=#" & Format(Me!txt_EndTime, 'yyyy-mm-dd') & "#"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vasnas]

Im afraid not, it doesnt work either...
keep in mind we'r talking about Times and not Dates...
So im not sure whether i use the 'yyyy-mm-dd', 'hh-mm' or what do i replace these with. But this looks promising, i think ive seen similar codes to this online. Anyway...

Record Time Range: [---------------------------------]
Search Time Range: [------------]

Only these above results should be produced.
I can't believe all my Access books and e-books dont provide the answer to this...
ty again!

 

[SkipVought]

"keep in mind we'r talking about Times and not Dates"

Huh???
[tt]
Date / TIME
Day 1 Day 2 Day 3
|-----|
[/tt]
otherwise
[tt]
TIME ONLY
Day 1 Day 2 Day 3
|-----| |-----| |-----|
[/tt]



Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[SkipVought]

"keep in mind we'r talking about Times and not Dates"

Huh???
[tt]
DATE & TIME
Date 1 Date 2 Date 3
|----|
[/tt]
otherwise
[tt]
TIME only
Date 1 Date 2 Date 3
|----| |----| |----|
[/tt]



Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[PHV]

Last attempt:

sql = sql & " AND Format(StartTime,'hh:nn:ss')<='" & Format(Me!txt_StartTime, 'hh:nn:ss') _
 & "' AND Format(EndTime,'hh:nn:ss')>='" & Format(Me!txt_EndTime, 'hh:nn:ss')  & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886