Help with Leave Date "window"?

[c9stealth]

I'm still working on a DataBase for my unit and I need to find a way to enter in two dates [startdate] [enddate] and have those dates hold the value of the time between them. say i enter in 01/01/2007 and 02/01/2007. and then do a date serch for 01/06/2007 threw 01/10/2007, I want the above start/end date to show up, because they are happening durring the serch timeframe. Hope this is understandable, I'm sorta confusing myself. heres the code I'm using at this time.

 >=[forms]![frmReports]![txtDateFrom] And <=[forms]![frmReports]![txtDateTo]

[MMcCarthy]

Try this ...

 BETWEEN [forms]![frmReports]![txtDateFrom] And [forms]![frmReports]![txtDateTo]

If either date is excluded (I can't remember) then ...

 BETWEEN [forms]![frmReports]![txtDateFrom] - 1 And [forms]![frmReports]![txtDateTo] + 1

Mary

[c9stealth]

Thanks for the Codes, but I didn't have any luck with them. Sorta got the same results that I'd been getting. I think maybe my problem isn't in the Code, but maybe in something else. I need to find a way to make my date range hold the value of everyday in the range so if i look at a day in the middle of the range it will tell me that this person is on leave durring that time. Right now I can only get dates that start in my search range. I do really thank you for your help though. I think maybe I'm just starting to get a bit out of my league. lol..

Try this ...

 BETWEEN [forms]![frmReports]![txtDateFrom] And [forms]![frmReports]![txtDateTo]

If either date is excluded (I can't remember) then ...

 BETWEEN [forms]![frmReports]![txtDateFrom] - 1 And [forms]![frmReports]![txtDateTo] + 1

Mary

[MMcCarthy]

Thanks for the Codes, but I didn't have any luck with them. Sorta got the same results that I'd been getting. I think maybe my problem isn't in the Code, but maybe in something else. I need to find a way to make my date range hold the value of everyday in the range so if i look at a day in the middle of the range it will tell me that this person is on leave durring that time. Right now I can only get dates that start in my search range. I do really thank you for your help though. I think maybe I'm just starting to get a bit out of my league. lol..

What's your table structures and how is this data recorded?

[c9stealth]

hmm... this is where you see how dumb I really am. By table Structures do you mean, that I have all the information I need comming from its own table. I am currently working with only one date [leavestartDate], I need a way to use the [leveenddate] in a range that will make all dates inbetween show up if searched. the data type is date/time imput mask for it is mm/dd/year

example:
soldier"A" leave dates are from 01/15/2007 - 02/20/2007

if i was to enter a search of the daterange for leave taking place the week of 01/21/2007 - 01/28/2007 I would want it to know that soldier"A" is onleave durring that time.

right now becasue the leave date for "A" starts on the 1st, it is excluded from my search. which sux. lol... I hope this information is helpful. I really appricate the time you are putting in to helping me out. I took a basic Microsoft Office class online and now my unit commander wants me to make this crazy database. I need to fly you up here to alaska and have you sit down and teach me how this all works!

What's your table structures and how is this data recorded?

[ADezii]

I'm still working on a DataBase for my unit and I need to find a way to enter in two dates [startdate] [enddate] and have those dates hold the value of the time between them. say i enter in 01/01/2007 and 02/01/2007. and then do a date serch for 01/06/2007 threw 01/10/2007, I want the above start/end date to show up, because they are happening durring the serch timeframe. Hope this is understandable, I'm sorta confusing myself. heres the code I'm using at this time.

 >=[forms]![frmReports]![txtDateFrom] And <=[forms]![frmReports]![txtDateTo]

Here is code that will store every value that is in your Date Range, then see if a Leave Date falls within the Range. Any questions feel free to ask.

Dim dteStartDate As Date, dteEndDate As Date
Dim DayDiff As Integer, intCounter As Integer, dteDateRange() As Date

dteStartDate = #12/1/2006#
dteEndDate = #12/31/2006#

DayDiff = DateDiff("d", dteStartDate, dteEndDate)

ReDim dteDateRange(DayDiff) As Date
dteDateRange(0) = dteStartDate

For intCounter = 1 To DayDiff
  dteDateRange(intCounter) = DateAdd("d", intCounter, dteStartDate)
Next intCounter
  
'See if Leave Date is in the Date Range
For intCounter = LBound(dteDateRange) To UBound(dteDateRange)
  If <your date> = dteDateRange(intCounter) Then     'Leave Date within Range
    Exit Sub
  End If
Next

[bibaudj]

Try working it out in 'English' first before progressing to code - that often helps and is the proper way to write code. Basically you are looking to find:

If StartDate is before Date1 and EndDate is after Date2
OR
if StartDate is between Date1 and Date2
OR
if EndDate is between Date1 and Date2

I think!

[NeoPa]

That's good advice. This sort of logic confuses the heck out of most people (hence very good advice).
What you're actually looking for is an overlap between two date ranges.
SearchFromDate to SearchToDate is the range being searced.
LeaveFromDate to LeaveToDate is the leave period.
So, what we are looking for is any record (Leave dates are stored in the table) where :
LeaveFromDate < SearchToDate
and
LeaveToDate > SearchFromDate.

[NeoPa]

Something along the lines of this should do you :

SELECT *
FROM LeaveTable
WHERE [StartDate]<=Form_frmReport!txtDateTo
  AND [EndDate]>=Form_frmReport!txtDateFrom

[c9stealth]

I still can't get it to work guys. As I said before I must be in over my head with wanting these results.

[NeoPa]

In that case we need to know where you're stuck.
It seems this is the full answer, but if there's something you don't understand we need to know which bit that is.

[c9stealth]

After many hours of pulling my hair out I finaly figured out the answer!! Here's the code I ended up with. I'm sure someone would of figured it out sooner if would of known how to ask the question. Thanks everyone for all of your help!! Hopefully now my commander wont think of anything else crazy for me to add to this database.

heres the final code:

[startdate]>=[forms]![frmReports]![txtDateFrom] And [enddate]<=[forms]![frmReports]![txtDateTo]

Thanks again to all who helped me!!

[NeoPa]

He's a commander - what else do you expect him to do :D

Glad it's sorted for you anyway :)

[NeoPa]

After many hours of pulling my hair out I finaly figured out the answer!! Here's the code I ended up with. I'm sure someone would of figured it out sooner if would of known how to ask the question. Thanks everyone for all of your help!! Hopefully now my commander wont think of anything else crazy for me to add to this database.

heres the final code:

[startdate]>=[forms]![frmReports]![txtDateFrom] And [enddate]<=[forms]![frmReports]![txtDateTo]

Thanks again to all who helped me!!

Hang on!
Unless I misunderstood the question (I don't think I did) this will not work perfectly.
This will actually find only those leave periods that are wholly within the range on the form. Is that what you want?

[NeoPa]

From post #8 you would need :

[StartDate]<=Form_frmReport!txtDateTo AND [EndDate]>=Form_frmReport!txtDateFrom

Can you let me know if this code (a direct replacement for your posted code) works for you.