Booking

I would be grateful for help with the following:

For a booking system

tblBook(BookedItemId,DateBooked)

tblBookingPeriodAllowed(PeriodName, PeriodStartDate, PeriodEndDate)

I want:

1. the DateBooked to be entered into txtDate on a bound form
frmBooking (source = tblBook)

2. DateBooked to be checked to see if it lies between any pair of
PeriodStartDate and PeriodEndDate

where a typical period lasts 12 weeks with 3 week gaps.
There several periods in tblBookingPeriodAllowed and so

3. If DateBooked lies within the allowed period, then it should be
accepted

4. If DateBooked lies outside of the allowed period, then it should be
rejected, with a message box indicating the fault.

Any ideas?

re: Booking

On 9 Mar 2006 12:32:13 -0800, "4004" <m.thomas57@ntlworld.com> wrote:

If BookingPeriods cannot overlap, then you could use DLookup:
DateBooked_BeforeUpdate(Cancel as Boolean)
dim varPeriod as Variant
varPeriod = DLookup("PeriodName","tblBookingPeriod", "PeriodStartDate
<= #" & txtDate & "# and PeriodEndDate >= #" & txtDate & "#") then
If IsNull(varPeriod) then
Msgbox "Not in a booking period!"
Cancel = True
else
Msgbox "In period " & varPeriod
end if

Note the use of the BeforeUpdate event. It's the one to be used for
field validation.

-Tom.

[color=blue]
>I would be grateful for help with the following:
>
>For a booking system
>
>tblBook(BookedItemId,DateBooked)
>
>tblBookingPeriodAllowed(PeriodName, PeriodStartDate, PeriodEndDate)
>
>I want:
>
>1. the DateBooked to be entered into txtDate on a bound form
>frmBooking (source = tblBook)
>
>2. DateBooked to be checked to see if it lies between any pair of
>PeriodStartDate and PeriodEndDate
>
>where a typical period lasts 12 weeks with 3 week gaps.
>There several periods in tblBookingPeriodAllowed and so
>
>3. If DateBooked lies within the allowed period, then it should be
>accepted
>
>4. If DateBooked lies outside of the allowed period, then it should be
>rejected, with a message box indicating the fault.
>
>Any ideas?[/color]

re: Booking

Thanks, that worked, it was just the 'magic bullet' function I was
looking for.

However, I used:

dlookup("PeriodName","tblBookingPeriod", "PeriodStartDate
<= txtDate and PeriodEndDate >= txtDate")

i.e. stripped out the # etc.

re: Booking

On 10 Mar 2006 14:16:50 -0800, "4004" <m.thomas57@ntlworld.com> wrote:

That often works. Good for you trying it.
I prefer the other syntax, which always works.

-Tom.


[color=blue]
>Thanks, that worked, it was just the 'magic bullet' function I was
>looking for.
>
>However, I used:
>
>dlookup("PeriodName","tblBookingPeriod", "PeriodStartDate
><= txtDate and PeriodEndDate >= txtDate")
>
>i.e. stripped out the # etc.[/color]