How to display rows that do not fall within 2 dates in a different

[JammyDodger]

I have two tables: a "Bookings" table and a "Vehicles" table. I would like to
create a query that displays all the vehicles which do not fall within 2
dates on the "Bookings" table.

So, an example: Vehicle1 from "Vehicles" is being used in a booking from 1
January 2010 to 10 January 2010. I don't want this vehicle to appear when
making a booking that falls between these 2 dates, as a vehicle cannot be
given to 2 people at the same time. I just want Vehicles 2, 3 and 4 to appear.

Also, if possible, I would like to add the condition that a Vehicle cannot
appear if it is being made in a Booking 4 days after it was returned. So, in
the previous example, Vehicle1 cannot appear for Bookings between 10 January
2010 and 14 January 2010.

I would be most extremely grateful for any help. Thanks!

 

[Marshall Barton]

I have two tables: a "Bookings" table and a "Vehicles" table. I would like to
create a query that displays all the vehicles which do not fall within 2
dates on the "Bookings" table.

So, an example: Vehicle1 from "Vehicles" is being used in a booking from 1
January 2010 to 10 January 2010. I don't want this vehicle to appear when
making a booking that falls between these 2 dates, as a vehicle cannot be
given to 2 people at the same time. I just want Vehicles 2, 3 and 4 to appear.

Also, if possible, I would like to add the condition that a Vehicle cannot
appear if it is being made in a Booking 4 days after it was returned. So, in
the previous example, Vehicle1 cannot appear for Bookings between 10 January
2010 and 14 January 2010.

I think you can use a query along these lines to do that:

SELECT Vehicles.*
FROM Vehicles
WHERE Vehicles.VehicleID NOT IN(
SELECT Bookings.VehicleID
FROM Bookings
WHERE Bookings.StartDate <= DateAdd("d", 4,
Forms!theform.txtEndDate)
AND Bookings.EndDate >= Forms!theform.txtStartDate)

 

[JammyDodger]

Hi Marshall,

Thanks, I tried using the code you supplied, but it asks for the start and
end dates when I run the query. I intend to use the query to provide the list
of Vehicles data for a Lookup field, so is there a way of doing it so that
it's automatic, and the list is created without user input??

 

[Marshall Barton]

The prompts are probably comming from the
Forms!theform.txtStartDate amd Forms!theform.txtEndDate
criteria. This is because you must have some way to enter
the dates when you want to find an available vehicle. There
can be no such thing as automatic because the dates are the
crux of the question the query is trying to answer.

I assumed that you were using a form with two text boxes to
enter those dates (in the header section?) and to display
the available vehicles (in the detail section).

If you do not have an arrangement something like that, then
you should explain what kind of UI you intend to use.

 

[Ken Sheridan]

Lets assume the Bookings table includes columns VehicleID, StartDate and
EndDate, the first being a foreign key referencing the primary key of the
Vehicles table. In a form bound to the bookings table include a combo box
bound to the VehicleID column. In the form's Current event procedure set the
RowSource of the combo box and disable/lock it as follows:

Dim crtl as Control

Set ctrl = Me.VehicleID

' move focus to StartDate Control
Me.StartDate.SetFocus

crtl.RowSource = "SELECT VehicleID FROM Vehicles"
ctrl.Requery
ctrl.Enabled = False
ctrl.Locked = True

This will ensure that the combo box can show the booked vehicle in existing
records, but cannot be updated by the user unless the booking dates are
changed.

In the AfterUpdate event procedures of both the StartDate and EndDate
controls add the following code:

Dim strCriteria As String
Dim crtl as Control

Set ctrl = Me.VehicleID

' firstly set the VehicleID to Null if either the start
' or end date control is updated
ctrl = Null

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
' build string expression to limit vehicles shown to those
' not already booked or within 4 days of return date
strCriteria = "(#" & Format(Me.StartDate, "yyyy-mm-dd") & _
"# Between StartDate And EndDate + 4 Or " & _
"#" & Format(Me.EndDate, "yyyy-mm-dd") & _
"# Between StartDate And EndDate + 4 Or " & _
"StartDate Between #" & _
Format(Me.StartDate, "yyyy-mm-dd") & "# And #" & _
Format(Me.EndDate, "yyyy-mm-dd") & "# Or " & _
"EndDate + 4 Between #" & _
Format(Me.StartDate, "yyyy-mm-dd") & "# And #" & _
Format(Me.EndDate, "yyyy-mm-dd") & "# )" & _
"And VehicleID = " & Me.VehicleID

' set VehicleID control's RowSource property to list
' only available vehicles
ctrlRowSource = SELECT VehicleID FROM Vehicles " & _
"WHERE NOT EXISTS " & _
"(SELECT * FROM Bookings " & _
"WHERE Bookings.VehicleID = Vehicles.VehicleID " & _
strCriteria & ") " & _
"ORDER BY Vehicle ID"

' requery and enable/unlock VehicleID control
ctrl.Requery
ctrl.Enabled = True
ctrl.Locked = False
End If

The above will list the VehicleID values in the combo box, but you may well
want to hide this bound column if it’s an arbitrary value such as an
autonumber, and show another column by amending the RowSource properties as
set in the above code, and by amending the ColumnCount and ColumnWidths
properties of the control.

Ken Sheridan
Stafford, England

 

[JammyDodger]

Well actually I intend to use an interface created in Delphi using Pascal,
rather than an Access-based interface. The two dates are stored in the
Bookings table, while a combobox displays the available vehicles for between
those dates. I'm really sorry to have led you and Ken Sheridan down the wrong
path...

I suppose instead I could make a query, displaying all those vehicles which
ARE being used between those two dates, then make another query which
displays all the vehicles not being displayed in the first query and makes no
reference to the dates? Instead of directly trying to make one single query
using the dates as source data?

The prompts are probably comming from the
Forms!theform.txtStartDate amd Forms!theform.txtEndDate
criteria. This is because you must have some way to enter
the dates when you want to find an available vehicle. There
can be no such thing as automatic because the dates are the
crux of the question the query is trying to answer.

I assumed that you were using a form with two text boxes to
enter those dates (in the header section?) and to display
the available vehicles (in the detail section).

If you do not have an arrangement something like that, then
you should explain what kind of UI you intend to use.
--
Marsh
MVP [MS Access]

Thanks, I tried using the code you supplied, but it asks for the start and
end dates when I run the query. I intend to use the query to provide the list
of Vehicles data for a Lookup field, so is there a way of doing it so that
it's automatic, and the list is created without user input??

 

[Ken Sheridan]

The underlying logic behind the expression I posted would still apply
regardless of the application used. It was originally written for booking
employee's leave and identifies overlapping date ranges, so in principle is
the same as your requirement. I just added the 4 days to cater for your
additional constraint.

Ken Sheridan
Stafford, England

Well actually I intend to use an interface created in Delphi using Pascal,
rather than an Access-based interface. The two dates are stored in the
Bookings table, while a combobox displays the available vehicles for between
those dates. I'm really sorry to have led you and Ken Sheridan down the wrong
path...

I suppose instead I could make a query, displaying all those vehicles which
ARE being used between those two dates, then make another query which
displays all the vehicles not being displayed in the first query and makes no
reference to the dates? Instead of directly trying to make one single query
using the dates as source data?

The prompts are probably comming from the
Forms!theform.txtStartDate amd Forms!theform.txtEndDate
criteria. This is because you must have some way to enter
the dates when you want to find an available vehicle. There
can be no such thing as automatic because the dates are the
crux of the question the query is trying to answer.

I assumed that you were using a form with two text boxes to
enter those dates (in the header section?) and to display
the available vehicles (in the detail section).

If you do not have an arrangement something like that, then
you should explain what kind of UI you intend to use.
--
Marsh
MVP [MS Access]

Thanks, I tried using the code you supplied, but it asks for the start and
end dates when I run the query. I intend to use the query to provide the list
of Vehicles data for a Lookup field, so is there a way of doing it so that
it's automatic, and the list is created without user input??

:

JammyDodger wrote:

I have two tables: a "Bookings" table and a "Vehicles" table. I would like to
create a query that displays all the vehicles which do not fall within 2
dates on the "Bookings" table.

So, an example: Vehicle1 from "Vehicles" is being used in a booking from 1
January 2010 to 10 January 2010. I don't want this vehicle to appear when
making a booking that falls between these 2 dates, as a vehicle cannot be
given to 2 people at the same time. I just want Vehicles 2, 3 and 4 to appear.

Also, if possible, I would like to add the condition that a Vehicle cannot
appear if it is being made in a Booking 4 days after it was returned. So, in
the previous example, Vehicle1 cannot appear for Bookings between 10 January
2010 and 14 January 2010.


I think you can use a query along these lines to do that:

SELECT Vehicles.*
FROM Vehicles
WHERE Vehicles.VehicleID NOT IN(
SELECT Bookings.VehicleID
FROM Bookings
WHERE Bookings.StartDate <= DateAdd("d", 4,
Forms!theform.txtEndDate)
AND Bookings.EndDate >= Forms!theform.txtStartDate)

 

[Marshall Barton]

Well actually I intend to use an interface created in Delphi using Pascal,
rather than an Access-based interface. The two dates are stored in the
Bookings table, while a combobox displays the available vehicles for between
those dates. I'm really sorry to have led you and Ken Sheridan down the wrong
path...

I suppose instead I could make a query, displaying all those vehicles which
ARE being used between those two dates, then make another query which
displays all the vehicles not being displayed in the first query and makes no
reference to the dates? Instead of directly trying to make one single query
using the dates as source data?

The query I posted does that using a subquery. The question
is where/how are the dates of interest entered/stored. You
just said they are in the bookings table, but that table
contains a list of vehicle IDs and the dates that the
vehicle has been booked.

The dates in question are when you want to find an available
vehicle. Isn't the kind of user question that we're trying
to answer "which vehicles are available from 4 April through
15 April?" The unbound form text boxes I used in the query
are where the 4 April and 15 April would be specified.
Regardless or where/how they are specified, the query that
answers that question can not come up with an answer without
that information coming from somewhere.

 

[JammyDodger]

Well I've had a scan through what you posted, and yes it does seem very good.
I'm having a bit of trouble understanding how to go about transforming it
into Delphi code, and some of the terminology. Could you tell me for example
what "strCriteria" and "Me" are, and which object "AfterUpdate" applies to
(i.e. form, button etc).

The underlying logic behind the expression I posted would still apply
regardless of the application used. It was originally written for booking
employee's leave and identifies overlapping date ranges, so in principle is
the same as your requirement. I just added the 4 days to cater for your
additional constraint.

Ken Sheridan
Stafford, England

Well actually I intend to use an interface created in Delphi using Pascal,
rather than an Access-based interface. The two dates are stored in the
Bookings table, while a combobox displays the available vehicles for between
those dates. I'm really sorry to have led you and Ken Sheridan down the wrong
path...

I suppose instead I could make a query, displaying all those vehicles which
ARE being used between those two dates, then make another query which
displays all the vehicles not being displayed in the first query and makes no
reference to the dates? Instead of directly trying to make one single query
using the dates as source data?

The prompts are probably comming from the
Forms!theform.txtStartDate amd Forms!theform.txtEndDate
criteria. This is because you must have some way to enter
the dates when you want to find an available vehicle. There
can be no such thing as automatic because the dates are the
crux of the question the query is trying to answer.

I assumed that you were using a form with two text boxes to
enter those dates (in the header section?) and to display
the available vehicles (in the detail section).

If you do not have an arrangement something like that, then
you should explain what kind of UI you intend to use.
--
Marsh
MVP [MS Access]


JammyDodger wrote:
Thanks, I tried using the code you supplied, but it asks for the start and
end dates when I run the query. I intend to use the query to provide the list
of Vehicles data for a Lookup field, so is there a way of doing it so that
it's automatic, and the list is created without user input??

:

JammyDodger wrote:

I have two tables: a "Bookings" table and a "Vehicles" table. I would like to
create a query that displays all the vehicles which do not fall within 2
dates on the "Bookings" table.

So, an example: Vehicle1 from "Vehicles" is being used in a booking from 1
January 2010 to 10 January 2010. I don't want this vehicle to appear when
making a booking that falls between these 2 dates, as a vehicle cannot be
given to 2 people at the same time. I just want Vehicles 2, 3 and 4 to appear.

Also, if possible, I would like to add the condition that a Vehicle cannot
appear if it is being made in a Booking 4 days after it was returned. So, in
the previous example, Vehicle1 cannot appear for Bookings between 10 January
2010 and 14 January 2010.


I think you can use a query along these lines to do that:

SELECT Vehicles.*
FROM Vehicles
WHERE Vehicles.VehicleID NOT IN(
SELECT Bookings.VehicleID
FROM Bookings
WHERE Bookings.StartDate <= DateAdd("d", 4,
Forms!theform.txtEndDate)
AND Bookings.EndDate >= Forms!theform.txtStartDate)