Re: Cars NOT rented
- From: Cor van der Bliek <CorvanderBliek@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 23 Nov 2006 13:35:01 -0800
Seems to work after I remove the ) in
"WHERE Customers.Startdate)= " & _
Thanks a lot!
"Douglas J. Steele" wrote:
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
http://I.Am/DougSteele
(no e-mails, please!)
"Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx> wrote in message
news:uWUDwQxDHHA.1304@xxxxxxxxxxxxxxxxxxxxxxx
What's your VBA code?
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Cor van der Bliek" <CorvanderBliek@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:1468EBA1-F2A0-41AE-8A93-39520C8EC896@xxxxxxxxxxxxxxxx
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?
"Douglas J. Steele" wrote:
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
http://I.Am/DougSteele
(no e-mails, please!)
"Cor van der Bliek" <CorvanderBliek@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:052F4EA8-AFD9-4A8B-8263-C7CA4B3D7C82@xxxxxxxxxxxxxxxx
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#));
"Larry Linson" wrote:
"Cor van der Bliek" <CorvanderBliek@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote
in
message news:EDDC9202-569C-43EA-9968-721406F07992@xxxxxxxxxxxxxxxx
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
- Follow-Ups:
- Re: Cars NOT rented
- From: Douglas J. Steele
- Re: Cars NOT rented
- References:
- Re: Cars NOT rented
- From: Larry Linson
- Re: Cars NOT rented
- From: Douglas J. Steele
- Re: Cars NOT rented
- From: Cor van der Bliek
- Re: Cars NOT rented
- From: Douglas J. Steele
- Re: Cars NOT rented
- From: Douglas J. Steele
- Re: Cars NOT rented
- Prev by Date: Re: Different results with opening form with docmd.
- Next by Date: Re: Cars NOT rented
- Previous by thread: Re: Cars NOT rented
- Next by thread: Re: Cars NOT rented
- Index(es):
Relevant Pages
|
