Prev: How do I call a Private Sub MyCode() from another place ?
Next: Importing Excel into an Access Form
From: Broadbonian on 18 Jun 2008 11:34
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
From: Albert D. Kallal on 18 Jun 2008 12:20
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....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal(a)msn.com



From: Broadbonian on 18 Jun 2008 12:36
Thanks so much, I will try this.
--
Darc


"Albert D. Kallal" wrote:

> 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....
>
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal(a)msn.com
>
>
>
>
From: Broadbonian on 19 Jun 2008 19:24
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.
--
Thanks, Darc


"Broadbonian" wrote:

> Thanks so much, I will try this.
> --
> Darc
>
>
> "Albert D. Kallal" wrote:
>
> > 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....
> >
> >
> > --
> > Albert D. Kallal (Access MVP)
> > Edmonton, Alberta Canada
> > pleaseNOOSpamKallal(a)msn.com
> >
> >
> >
> >
 | 
Pages: 1
Prev: How do I call a Private Sub MyCode() from another place ?
Next: Importing Excel into an Access Form