code between date range

[Broadbonian]

In my report "rSchedule" I have 2 text box-tbEmpStDt and tbEmpEnDt. The
data type is binary, comes from a make table query. I need to compare those
dates with text box startdate and enddate in my open form "frmDates". This
form will always have Sun thru Sat as a date range. In the report I have a
text box for each day of the week. In each box I would like to display "WRK"
if the tbEmpStDt and tbEmpEnDt start date and or end date fall on or within
the range of frmDates.

frmDates startdate=6/8/2008
frmDates enddate=6/14/2008

Report tbEmpStDt=6/10/2008
Report tbEmpEnDt=6/11/2008

The text boxes of Sun thru Monday would only display "WRK" in Tues and Wed
cells, the others would be blank.

Thank you in advance.

Darc

 

[Albert D. Kallal]

To prevent collisions, the logic here is quite simple:


A collision occurs when:


RequestStartDate <= EndDate
and
RequestEndDate >= StartDate


The above is thus a rather simply query, but if any collision occurs, the
above will return records..and you simply don't allow the booking. In other
words, since we NEVER allow booking with a collision, then the above simply
statement will work for us.


dim strWhere as string
dim dtRequeestStartDate as date
dim dtRequestEndDate as date


dtRequestStartDate = inputbox("Enter start Date")
dtRequestEndDate = inputbox("Enter end date")


strWhere="#" & format(dtRequestStartDate,"mm/­dd/yyyy") & "# <= EndDate" & _
" and #" & format(dtRequestEndDate,"mm/dd­/yyyy") & "# >= StartDate"


if dcount("*","tableBooking",strW­here) > 0 then
msgbox "sorry, you can't book
....bla bla bla....


The above is just an example, and I am sure you would build a nice form that
prompts the user for the booking dates. However, what is nice here is that
the simple conditions above does return ANY collisions....

 

[Broadbonian]

Thanks so much, I will try this.

 

[Broadbonian]

Hi Albert, I added the code, but it gives an error message wanting a table or
query called "booking". Not sure how to go from this to getting the "WRK"
message displayed in the text box of the report. Appreciate any help if you
have the time.