Prev: Graphics in Report
Next: Custom Label for data series with calculation
From: Broadbonian on 20 Jun 2008 20:11
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....

--

 | 
Pages: 1
Prev: Graphics in Report
Next: Custom Label for data series with calculation