B
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.
I did receive this from Albert, but it does not quite do everything. I have
been trying to finish the code, but it is beyond me. Any help is appreciated.
Thank you Darc
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....
--
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.
I did receive this from Albert, but it does not quite do everything. I have
been trying to finish the code, but it is beyond me. Any help is appreciated.
Thank you Darc
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....
--