Booking

[4004]

I would be grateful for help with the following:

For a booking system

tblBook(BookedI temId,DateBooke d)

tblBookingPerio dAllowed(Period Name, 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 tblBookingPerio dAllowed 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?

[Tom van Stiphout]

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

If BookingPeriods cannot overlap, then you could use DLookup:
DateBooked_Befo reUpdate(Cancel as Boolean)
dim varPeriod as Variant
varPeriod = DLookup("Period Name","tblBooki ngPeriod", "PeriodStartDat e
<= #" & txtDate & "# and PeriodEndDate >= #" & txtDate & "#") then
If IsNull(varPerio d) 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.

I would be grateful for help with the following:

For a booking system

tblBook(Booked ItemId,DateBook ed)

tblBookingPeri odAllowed(Perio dName, 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
PeriodStartDat e and PeriodEndDate

where a typical period lasts 12 weeks with 3 week gaps.
There several periods in tblBookingPerio dAllowed 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?

[4004]

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

However, I used:

dlookup("Period Name","tblBooki ngPeriod", "PeriodStartDat e
<= txtDate and PeriodEndDate >= txtDate")

i.e. stripped out the # etc.

[Tom van Stiphout]

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

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

-Tom.

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

However, I used:

dlookup("Perio dName","tblBook ingPeriod", "PeriodStartDat e
<= txtDate and PeriodEndDate >= txtDate")

i.e. stripped out the # etc.