SQL Left Join Problem

[turk39]

I am working on a system for reservations and I want to be able to produce a result that lists the dates for the next 10 days and then show which days have reservations to feed to a datagrid in VB.NET. (Database is Access2000)
I first created a table of days to join my reservations to
with just the dates.
Then I have a table with the reservations that contains the
date, resource-ID, and user to be reserved.
I cannot come up with a query that selects a date range and a specific resource-ID and user then shows the following:

Resource = 101

Date User
1/12/03 (blank = no one is using)
1/13/03 Bob
1/14/03
1/15/03 John
etc.

 

[Transcend]

What have you tried so far?

and could you paste your table structure here?

Transcend

 

[turk39]

ResDate Table Reservation Table
[ResDate] [ResDate] [Resource] [User]
1/12/03 1/13/03 101 MB
1/13/03 1/13/03 102 BR
1/14/03 1/15/03 101 MB
etc. 1/16/03 102 BR
1/16/03 101 MB
Would like to request 1/12 thru 1/17 for 101 and get:
1/12/03 <blank>
1/13/03 MB
1/14/03 <blank>
1/15/03 MB
1/16/03 MB
1/17/03 <blank>

 

[turk39]

Here is what I have tried:

SELECT ResDate.ResDate, Reservation.ResourceID, Reservation.User
FROM ResDate LEFT JOIN Reservation ON ResDate.ResDate = Reservation.ResDate
WHERE (((ResDate.ResDate) Between #1/12/2003# And #1/20/2003#) AND ((Reservation.ResourceID)=&quot;101&quot);


It does not show the blank days for me.