Cars NOT rented

[Cor van der Bliek]

I have a table 'cars' (Id,Car name) and a table 'rentals' (Id, Start date,
End date, Carid, Person).
I want to populate a combolist with the cars NOT rented for a specific date.

Thanks for helping.

 

[tina]

use the Query Wizard to help you write an Unmatched query, to return only
those cars that are not in the rental table. presumably you'll need to set
criteria on the rentals table's EndDate field as "Is Null", so that only
cars with "open" rental records will be filtered out.

hth

 

[Larry Linson]

I have a table 'cars' (Id,Car name) and a table 'rentals' (Id, Start date,
End date, Carid, Person).
I want to populate a combolist with the cars NOT rented for a specific
date.

Assuming you first validate that the Specific Date is not already past

Create a Query joining table Cars to table Rentals, on CarId with the Link
Properties "All Records from table Cars, and only those that match from
table Rentals".

Criteria:

(Start Date > SpecificDate) OR (IsNull(StartDate))

-- rental does not start before the specific date or there is no start date

OR

(EndDate<SpecificDate)

-- or rental ends before the specific date for which the car is desired

This assumes that a car is rented or reserved for a specific period with
both a start and an end date. I'd think that you really need to test for
Specific DateS (plural) a SpecificStartDate and a SpecificEndDate...
otherwise you might find that the car is available but is _reserved_ for
dates that would overlap the needed specific dates.

Larry Linson
Microsoft Access

 

[Tom]

If you run a query which listed all cars which had null as a Start Date, you
would have a list of cars actually available for hire. The vehicle
registration number would be the more usual way of identifying a vehicle
than a name.

Tom

 

[Cor van der Bliek]

First of all, when I stated Carname I really meant license plate:

Your query, Larry, doesn't seem to do the trick.

What I'm looking for are all the cars NOT fitting this simple query:

SELECT [Cars].Type, [Cars].License
FROM [Cars] LEFT JOIN Customers ON [Cars].Id = Customers.LicenseId
WHERE (((Customers.Startdate)=#11/20/2006#));

 

[Douglas J. Steele]

SELECT [Cars].Type, [Cars].License
FROM [Cars]
LEFT JOIN
(
SELECT [Cars].Type, [Cars].License
FROM [Cars] LEFT JOIN Customers ON [Cars].Id = Customers.LicenseId
WHERE (((Customers.Startdate)=#11/20/2006#));
) AS SubQuery
ON [Cars].License = SubQuery.License
WHERE SubQuery.License IS NULL

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

First of all, when I stated Carname I really meant license plate:

Your query, Larry, doesn't seem to do the trick.

What I'm looking for are all the cars NOT fitting this simple query:

SELECT [Cars].Type, [Cars].License
FROM [Cars] LEFT JOIN Customers ON [Cars].Id = Customers.LicenseId
WHERE (((Customers.Startdate)=#11/20/2006#));

Assuming you first validate that the Specific Date is not already past

Create a Query joining table Cars to table Rentals, on CarId with the
Link
Properties "All Records from table Cars, and only those that match from
table Rentals".

Criteria:

(Start Date > SpecificDate) OR (IsNull(StartDate))

-- rental does not start before the specific date or there is no start
date

OR

(EndDate<SpecificDate)

-- or rental ends before the specific date for which the car is desired

This assumes that a car is rented or reserved for a specific period with
both a start and an end date. I'd think that you really need to test for
Specific DateS (plural) a SpecificStartDate and a SpecificEndDate...
otherwise you might find that the car is available but is _reserved_ for
dates that would overlap the needed specific dates.

Larry Linson
Microsoft Access

 

[Cor van der Bliek]

Thanks, it works in the query window: I get the desired cars fot the chosen
Startdate, but when I put the code in VBA (replacing the date with
Me.Startdate) nothing happens.
Are there different considerations to be aware of?

SELECT [Cars].Type, [Cars].License
FROM [Cars]
LEFT JOIN
(
SELECT [Cars].Type, [Cars].License
FROM [Cars] LEFT JOIN Customers ON [Cars].Id = Customers.LicenseId
WHERE (((Customers.Startdate)=#11/20/2006#));
) AS SubQuery
ON [Cars].License = SubQuery.License
WHERE SubQuery.License IS NULL

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

First of all, when I stated Carname I really meant license plate:

Your query, Larry, doesn't seem to do the trick.

What I'm looking for are all the cars NOT fitting this simple query:

SELECT [Cars].Type, [Cars].License
FROM [Cars] LEFT JOIN Customers ON [Cars].Id = Customers.LicenseId
WHERE (((Customers.Startdate)=#11/20/2006#));

message I have a table 'cars' (Id,Car name) and a table 'rentals' (Id, Start
date,
End date, Carid, Person).
I want to populate a combolist with the cars NOT rented for a specific
date.

Assuming you first validate that the Specific Date is not already past

Create a Query joining table Cars to table Rentals, on CarId with the
Link
Properties "All Records from table Cars, and only those that match from
table Rentals".

Criteria:

(Start Date > SpecificDate) OR (IsNull(StartDate))

-- rental does not start before the specific date or there is no start
date

OR

(EndDate<SpecificDate)

-- or rental ends before the specific date for which the car is desired

This assumes that a car is rented or reserved for a specific period with
both a start and an end date. I'd think that you really need to test for
Specific DateS (plural) a SpecificStartDate and a SpecificEndDate...
otherwise you might find that the car is available but is _reserved_ for
dates that would overlap the needed specific dates.

Larry Linson
Microsoft Access

 

[Douglas J. Steele]

What's your VBA code?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, it works in the query window: I get the desired cars fot the
chosen
Startdate, but when I put the code in VBA (replacing the date with
Me.Startdate) nothing happens.
Are there different considerations to be aware of?

SELECT [Cars].Type, [Cars].License
FROM [Cars]
LEFT JOIN
(
SELECT [Cars].Type, [Cars].License
FROM [Cars] LEFT JOIN Customers ON [Cars].Id = Customers.LicenseId
WHERE (((Customers.Startdate)=#11/20/2006#));
) AS SubQuery
ON [Cars].License = SubQuery.License
WHERE SubQuery.License IS NULL

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

First of all, when I stated Carname I really meant license plate:

Your query, Larry, doesn't seem to do the trick.

What I'm looking for are all the cars NOT fitting this simple query:

SELECT [Cars].Type, [Cars].License
FROM [Cars] LEFT JOIN Customers ON [Cars].Id = Customers.LicenseId
WHERE (((Customers.Startdate)=#11/20/2006#));

:


in
message I have a table 'cars' (Id,Car name) and a table 'rentals' (Id, Start
date,
End date, Carid, Person).
I want to populate a combolist with the cars NOT rented for a
specific
date.

Assuming you first validate that the Specific Date is not already past

Create a Query joining table Cars to table Rentals, on CarId with the
Link
Properties "All Records from table Cars, and only those that match
from
table Rentals".

Criteria:

(Start Date > SpecificDate) OR (IsNull(StartDate))

-- rental does not start before the specific date or there is no start
date

OR

(EndDate<SpecificDate)

-- or rental ends before the specific date for which the car is
desired

This assumes that a car is rented or reserved for a specific period
with
both a start and an end date. I'd think that you really need to test
for
Specific DateS (plural) a SpecificStartDate and a SpecificEndDate...
otherwise you might find that the car is available but is _reserved_
for
dates that would overlap the needed specific dates.

Larry Linson
Microsoft Access

 

[Douglas J. Steele]

Just in case the error is how you're referring to Me.startdate, you'd build
the SQL like:

Dim strSQL As String

strSQL = "SELECT [Cars].Type, [Cars].License " & _
"FROM [Cars] " & _
"LEFT JOIN " & _
"(" & _
"SELECT [Cars].Type, [Cars].License " & _
"FROM [Cars] LEFT JOIN Customers " & _
"ON [Cars].Id = Customers.LicenseId " & _
"WHERE Customers.Startdate)= " & _
Format(Me.Startdate, "\#mm\/dd\/yyyy\#") & _
") AS SubQuery " & _
"ON [Cars].License = SubQuery.License " & _
"WHERE SubQuery.License IS NULL "


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

What's your VBA code?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, it works in the query window: I get the desired cars fot the
chosen
Startdate, but when I put the code in VBA (replacing the date with
Me.Startdate) nothing happens.
Are there different considerations to be aware of?

SELECT [Cars].Type, [Cars].License
FROM [Cars]
LEFT JOIN
(
SELECT [Cars].Type, [Cars].License
FROM [Cars] LEFT JOIN Customers ON [Cars].Id = Customers.LicenseId
WHERE (((Customers.Startdate)=#11/20/2006#));
) AS SubQuery
ON [Cars].License = SubQuery.License
WHERE SubQuery.License IS NULL

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message First of all, when I stated Carname I really meant license plate:

Your query, Larry, doesn't seem to do the trick.

What I'm looking for are all the cars NOT fitting this simple query:

SELECT [Cars].Type, [Cars].License
FROM [Cars] LEFT JOIN Customers ON [Cars].Id = Customers.LicenseId
WHERE (((Customers.Startdate)=#11/20/2006#));

:


in
message I have a table 'cars' (Id,Car name) and a table 'rentals' (Id, Start
date,
End date, Carid, Person).
I want to populate a combolist with the cars NOT rented for a
specific
date.

Assuming you first validate that the Specific Date is not already
past

Create a Query joining table Cars to table Rentals, on CarId with the
Link
Properties "All Records from table Cars, and only those that match
from
table Rentals".

Criteria:

(Start Date > SpecificDate) OR (IsNull(StartDate))

-- rental does not start before the specific date or there is no
start
date

OR

(EndDate<SpecificDate)

-- or rental ends before the specific date for which the car is
desired

This assumes that a car is rented or reserved for a specific period
with
both a start and an end date. I'd think that you really need to test
for
Specific DateS (plural) a SpecificStartDate and a SpecificEndDate...
otherwise you might find that the car is available but is _reserved_
for
dates that would overlap the needed specific dates.

Larry Linson
Microsoft Access

 

[Cor van der Bliek]

Seems to work after I remove the ) in
"WHERE Customers.Startdate)= " & _

Thanks a lot!

Just in case the error is how you're referring to Me.startdate, you'd build
the SQL like:

Dim strSQL As String

strSQL = "SELECT [Cars].Type, [Cars].License " & _
"FROM [Cars] " & _
"LEFT JOIN " & _
"(" & _
"SELECT [Cars].Type, [Cars].License " & _
"FROM [Cars] LEFT JOIN Customers " & _
"ON [Cars].Id = Customers.LicenseId " & _
"WHERE Customers.Startdate)= " & _
Format(Me.Startdate, "\#mm\/dd\/yyyy\#") & _
") AS SubQuery " & _
"ON [Cars].License = SubQuery.License " & _
"WHERE SubQuery.License IS NULL "


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

What's your VBA code?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, it works in the query window: I get the desired cars fot the
chosen
Startdate, but when I put the code in VBA (replacing the date with
Me.Startdate) nothing happens.
Are there different considerations to be aware of?

:

SELECT [Cars].Type, [Cars].License
FROM [Cars]
LEFT JOIN
(
SELECT [Cars].Type, [Cars].License
FROM [Cars] LEFT JOIN Customers ON [Cars].Id = Customers.LicenseId
WHERE (((Customers.Startdate)=#11/20/2006#));
) AS SubQuery
ON [Cars].License = SubQuery.License
WHERE SubQuery.License IS NULL

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message First of all, when I stated Carname I really meant license plate:

Your query, Larry, doesn't seem to do the trick.

What I'm looking for are all the cars NOT fitting this simple query:

SELECT [Cars].Type, [Cars].License
FROM [Cars] LEFT JOIN Customers ON [Cars].Id = Customers.LicenseId
WHERE (((Customers.Startdate)=#11/20/2006#));

:


in
message I have a table 'cars' (Id,Car name) and a table 'rentals' (Id, Start
date,
End date, Carid, Person).
I want to populate a combolist with the cars NOT rented for a
specific
date.

Assuming you first validate that the Specific Date is not already
past

Create a Query joining table Cars to table Rentals, on CarId with the
Link
Properties "All Records from table Cars, and only those that match
from
table Rentals".

Criteria:

(Start Date > SpecificDate) OR (IsNull(StartDate))

-- rental does not start before the specific date or there is no
start
date

OR

(EndDate<SpecificDate)

-- or rental ends before the specific date for which the car is
desired

This assumes that a car is rented or reserved for a specific period
with
both a start and an end date. I'd think that you really need to test
for
Specific DateS (plural) a SpecificStartDate and a SpecificEndDate...
otherwise you might find that the car is available but is _reserved_
for
dates that would overlap the needed specific dates.

Larry Linson
Microsoft Access

 

[Douglas J. Steele]

Sorry: my fault. I tried to remove the extraneous parentheses Access is so
found of inserting, and I obviously missed one!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Seems to work after I remove the ) in
"WHERE Customers.Startdate)= " & _

Thanks a lot!

Just in case the error is how you're referring to Me.startdate, you'd
build
the SQL like:

Dim strSQL As String

strSQL = "SELECT [Cars].Type, [Cars].License " & _
"FROM [Cars] " & _
"LEFT JOIN " & _
"(" & _
"SELECT [Cars].Type, [Cars].License " & _
"FROM [Cars] LEFT JOIN Customers " & _
"ON [Cars].Id = Customers.LicenseId " & _
"WHERE Customers.Startdate)= " & _
Format(Me.Startdate, "\#mm\/dd\/yyyy\#") & _
") AS SubQuery " & _
"ON [Cars].License = SubQuery.License " & _
"WHERE SubQuery.License IS NULL "


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

What's your VBA code?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message Thanks, it works in the query window: I get the desired cars fot the
chosen
Startdate, but when I put the code in VBA (replacing the date with
Me.Startdate) nothing happens.
Are there different considerations to be aware of?

:

SELECT [Cars].Type, [Cars].License
FROM [Cars]
LEFT JOIN
(
SELECT [Cars].Type, [Cars].License
FROM [Cars] LEFT JOIN Customers ON [Cars].Id = Customers.LicenseId
WHERE (((Customers.Startdate)=#11/20/2006#));
) AS SubQuery
ON [Cars].License = SubQuery.License
WHERE SubQuery.License IS NULL

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


in
message First of all, when I stated Carname I really meant license plate:

Your query, Larry, doesn't seem to do the trick.

What I'm looking for are all the cars NOT fitting this simple
query:

SELECT [Cars].Type, [Cars].License
FROM [Cars] LEFT JOIN Customers ON [Cars].Id = Customers.LicenseId
WHERE (((Customers.Startdate)=#11/20/2006#));

:


"Cor van der Bliek" <[email protected]>
wrote
in
message I have a table 'cars' (Id,Car name) and a table 'rentals' (Id,
Start
date,
End date, Carid, Person).
I want to populate a combolist with the cars NOT rented for a
specific
date.

Assuming you first validate that the Specific Date is not already
past

Create a Query joining table Cars to table Rentals, on CarId with
the
Link
Properties "All Records from table Cars, and only those that match
from
table Rentals".

Criteria:

(Start Date > SpecificDate) OR (IsNull(StartDate))

-- rental does not start before the specific date or there is no
start
date

OR

(EndDate<SpecificDate)

-- or rental ends before the specific date for which the car is
desired

This assumes that a car is rented or reserved for a specific
period
with
both a start and an end date. I'd think that you really need to
test
for
Specific DateS (plural) a SpecificStartDate and a
SpecificEndDate...
otherwise you might find that the car is available but is
_reserved_
for
dates that would overlap the needed specific dates.

Larry Linson
Microsoft Access

 

[Cor van der Bliek]

No problem. Keeps me sharp.

Sorry: my fault. I tried to remove the extraneous parentheses Access is so
found of inserting, and I obviously missed one!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Seems to work after I remove the ) in
"WHERE Customers.Startdate)= " & _

Thanks a lot!

Just in case the error is how you're referring to Me.startdate, you'd
build
the SQL like:

Dim strSQL As String

strSQL = "SELECT [Cars].Type, [Cars].License " & _
"FROM [Cars] " & _
"LEFT JOIN " & _
"(" & _
"SELECT [Cars].Type, [Cars].License " & _
"FROM [Cars] LEFT JOIN Customers " & _
"ON [Cars].Id = Customers.LicenseId " & _
"WHERE Customers.Startdate)= " & _
Format(Me.Startdate, "\#mm\/dd\/yyyy\#") & _
") AS SubQuery " & _
"ON [Cars].License = SubQuery.License " & _
"WHERE SubQuery.License IS NULL "


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


What's your VBA code?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message Thanks, it works in the query window: I get the desired cars fot the
chosen
Startdate, but when I put the code in VBA (replacing the date with
Me.Startdate) nothing happens.
Are there different considerations to be aware of?

:

SELECT [Cars].Type, [Cars].License
FROM [Cars]
LEFT JOIN
(
SELECT [Cars].Type, [Cars].License
FROM [Cars] LEFT JOIN Customers ON [Cars].Id = Customers.LicenseId
WHERE (((Customers.Startdate)=#11/20/2006#));
) AS SubQuery
ON [Cars].License = SubQuery.License
WHERE SubQuery.License IS NULL

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


in
message First of all, when I stated Carname I really meant license plate:

Your query, Larry, doesn't seem to do the trick.

What I'm looking for are all the cars NOT fitting this simple
query:

SELECT [Cars].Type, [Cars].License
FROM [Cars] LEFT JOIN Customers ON [Cars].Id = Customers.LicenseId
WHERE (((Customers.Startdate)=#11/20/2006#));

:


"Cor van der Bliek" <[email protected]>
wrote
in
message I have a table 'cars' (Id,Car name) and a table 'rentals' (Id,
Start
date,
End date, Carid, Person).
I want to populate a combolist with the cars NOT rented for a
specific
date.

Assuming you first validate that the Specific Date is not already
past

Create a Query joining table Cars to table Rentals, on CarId with
the
Link
Properties "All Records from table Cars, and only those that match
from
table Rentals".

Criteria:

(Start Date > SpecificDate) OR (IsNull(StartDate))

-- rental does not start before the specific date or there is no
start
date

OR

(EndDate<SpecificDate)

-- or rental ends before the specific date for which the car is
desired

This assumes that a car is rented or reserved for a specific
period
with
both a start and an end date. I'd think that you really need to
test
for
Specific DateS (plural) a SpecificStartDate and a
SpecificEndDate...
otherwise you might find that the car is available but is
_reserved_
for
dates that would overlap the needed specific dates.

Larry Linson
Microsoft Access

 

[Cor van der Bliek]

Whenever I change anything -anything at all- in het query, I get an error:

SELECT [Cars].Type, [Cars].License
FROM [Cars] LEFT JOIN [SELECT [Cars].Type, [Cars].License
FROM [Cars] LEFT JOIN Customers ON [Cars].Id = Customers.LicenseId
WHERE (((Customers.Startdate)=#11/20/2006#));
]. AS SubQuery ON [Cars].License = SubQuery.License
WHERE SubQuery.License IS NULL

The FROM component contains a syntax error.

I want to change the query, since there is a possibility that the planned
rental period exceeds one day. Changing
WHERE (((Customers.Startdate)=#11/20/2006#)); to
WHERE (((Customers.Startdate)>=#11/20/2006# and
Customers.Plandate)<=#11/20/2006#));
would do the trick I hoped.

No problem. Keeps me sharp.

Sorry: my fault. I tried to remove the extraneous parentheses Access is so
found of inserting, and I obviously missed one!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Seems to work after I remove the ) in
"WHERE Customers.Startdate)= " & _

Thanks a lot!

:

Just in case the error is how you're referring to Me.startdate, you'd
build
the SQL like:

Dim strSQL As String

strSQL = "SELECT [Cars].Type, [Cars].License " & _
"FROM [Cars] " & _
"LEFT JOIN " & _
"(" & _
"SELECT [Cars].Type, [Cars].License " & _
"FROM [Cars] LEFT JOIN Customers " & _
"ON [Cars].Id = Customers.LicenseId " & _
"WHERE Customers.Startdate)= " & _
Format(Me.Startdate, "\#mm\/dd\/yyyy\#") & _
") AS SubQuery " & _
"ON [Cars].License = SubQuery.License " & _
"WHERE SubQuery.License IS NULL "


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


What's your VBA code?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message Thanks, it works in the query window: I get the desired cars fot the
chosen
Startdate, but when I put the code in VBA (replacing the date with
Me.Startdate) nothing happens.
Are there different considerations to be aware of?

:

SELECT [Cars].Type, [Cars].License
FROM [Cars]
LEFT JOIN
(
SELECT [Cars].Type, [Cars].License
FROM [Cars] LEFT JOIN Customers ON [Cars].Id = Customers.LicenseId
WHERE (((Customers.Startdate)=#11/20/2006#));
) AS SubQuery
ON [Cars].License = SubQuery.License
WHERE SubQuery.License IS NULL

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


in
message First of all, when I stated Carname I really meant license plate:

Your query, Larry, doesn't seem to do the trick.

What I'm looking for are all the cars NOT fitting this simple
query:

SELECT [Cars].Type, [Cars].License
FROM [Cars] LEFT JOIN Customers ON [Cars].Id = Customers.LicenseId
WHERE (((Customers.Startdate)=#11/20/2006#));

:


"Cor van der Bliek" <[email protected]>
wrote
in
message I have a table 'cars' (Id,Car name) and a table 'rentals' (Id,
Start
date,
End date, Carid, Person).
I want to populate a combolist with the cars NOT rented for a
specific
date.

Assuming you first validate that the Specific Date is not already
past

Create a Query joining table Cars to table Rentals, on CarId with
the
Link
Properties "All Records from table Cars, and only those that match
from
table Rentals".

Criteria:

(Start Date > SpecificDate) OR (IsNull(StartDate))

-- rental does not start before the specific date or there is no
start
date

OR

(EndDate<SpecificDate)

-- or rental ends before the specific date for which the car is
desired

This assumes that a car is rented or reserved for a specific
period
with
both a start and an end date. I'd think that you really need to
test
for
Specific DateS (plural) a SpecificStartDate and a
SpecificEndDate...
otherwise you might find that the car is available but is
_reserved_
for
dates that would overlap the needed specific dates.

Larry Linson
Microsoft Access

 

[Douglas J. Steele]

First, get rid of the semi-colon in the subquery.

In my original suggestion, I had a open parenthesis after the first LEFT
JOIN statement (and a close parenthesis in front of SubQuery, where you now
have ].)

In your second example, your parentheses are incorrect. All you need is

WHERE (Customers.Startdate>=#11/20/2006# and
Customers.Plandate<=#11/20/2006#)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Whenever I change anything -anything at all- in het query, I get an error:

SELECT [Cars].Type, [Cars].License
FROM [Cars] LEFT JOIN [SELECT [Cars].Type, [Cars].License
FROM [Cars] LEFT JOIN Customers ON [Cars].Id = Customers.LicenseId
WHERE (((Customers.Startdate)=#11/20/2006#));
]. AS SubQuery ON [Cars].License = SubQuery.License
WHERE SubQuery.License IS NULL

The FROM component contains a syntax error.

I want to change the query, since there is a possibility that the planned
rental period exceeds one day. Changing
WHERE (((Customers.Startdate)=#11/20/2006#)); to
WHERE (((Customers.Startdate)>=#11/20/2006# and
Customers.Plandate)<=#11/20/2006#));
would do the trick I hoped.

No problem. Keeps me sharp.
On several occasions in this particular form -nothing changes- an error
in
my FROM is reported. Any ideas on that?

Sorry: my fault. I tried to remove the extraneous parentheses Access is
so
found of inserting, and I obviously missed one!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message Seems to work after I remove the ) in
"WHERE Customers.Startdate)= " & _

Thanks a lot!

:

Just in case the error is how you're referring to Me.startdate,
you'd
build
the SQL like:

Dim strSQL As String

strSQL = "SELECT [Cars].Type, [Cars].License " & _
"FROM [Cars] " & _
"LEFT JOIN " & _
"(" & _
"SELECT [Cars].Type, [Cars].License " & _
"FROM [Cars] LEFT JOIN Customers " & _
"ON [Cars].Id = Customers.LicenseId " & _
"WHERE Customers.Startdate)= " & _
Format(Me.Startdate, "\#mm\/dd\/yyyy\#") & _
") AS SubQuery " & _
"ON [Cars].License = SubQuery.License " & _
"WHERE SubQuery.License IS NULL "


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
What's your VBA code?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Cor van der Bliek" <[email protected]>
wrote in
message Thanks, it works in the query window: I get the desired cars fot
the
chosen
Startdate, but when I put the code in VBA (replacing the date
with
Me.Startdate) nothing happens.
Are there different considerations to be aware of?

:

SELECT [Cars].Type, [Cars].License
FROM [Cars]
LEFT JOIN
(
SELECT [Cars].Type, [Cars].License
FROM [Cars] LEFT JOIN Customers ON [Cars].Id =
Customers.LicenseId
WHERE (((Customers.Startdate)=#11/20/2006#));
) AS SubQuery
ON [Cars].License = SubQuery.License
WHERE SubQuery.License IS NULL

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Cor van der Bliek" <[email protected]>
wrote
in
message
First of all, when I stated Carname I really meant license
plate:

Your query, Larry, doesn't seem to do the trick.

What I'm looking for are all the cars NOT fitting this simple
query:

SELECT [Cars].Type, [Cars].License
FROM [Cars] LEFT JOIN Customers ON [Cars].Id =
Customers.LicenseId
WHERE (((Customers.Startdate)=#11/20/2006#));

:


"Cor van der Bliek"
<[email protected]>
wrote
in
message
I have a table 'cars' (Id,Car name) and a table 'rentals'
(Id,
Start
date,
End date, Carid, Person).
I want to populate a combolist with the cars NOT rented for
a
specific
date.

Assuming you first validate that the Specific Date is not
already
past

Create a Query joining table Cars to table Rentals, on CarId
with
the
Link
Properties "All Records from table Cars, and only those that
match
from
table Rentals".

Criteria:

(Start Date > SpecificDate) OR (IsNull(StartDate))

-- rental does not start before the specific date or there is
no
start
date

OR

(EndDate<SpecificDate)

-- or rental ends before the specific date for which the car
is
desired

This assumes that a car is rented or reserved for a specific
period
with
both a start and an end date. I'd think that you really need
to
test
for
Specific DateS (plural) a SpecificStartDate and a
SpecificEndDate...
otherwise you might find that the car is available but is
_reserved_
for
dates that would overlap the needed specific dates.

Larry Linson
Microsoft Access