Need to display dates no one worked on a project

[galaxy]

I have a table that records when someone has started and stopped working on a project.

The fields are Project, StartTime, EndTime.
The start/end time fields are Long Dates.

The sample data is something like
Walk Dog, 2/22/2013 11:00, 2/22/2013 11:30
Walk Dog, 2/22/2013 19:00, 2/22/2013 19:30
Walk Dog, 2/24/2013 11:00, 2/24/2013 11:30
Walk Dog, 2/26/2013 19:00, 2/26/2013 19:30

The dog was not walked at all on the 23rd or the 25th, and I need a query that shows this, something like the following.

2/22 Walk Dog 1 hr
2/23 Walk Dog ---
2/24 Walk Dog 30 min
2/25 Walk Dog ---
2/26 Walk Dog 30 min

 

[plog]

Access can't create values that don't exist. Do the values 2/23 or 2/25 appear anywhere in your database? If not, you are going to have to create a datasource to contain them.

One way to do this is to create a table of dates, let's call it ReportDates. It would be have one field--ReportDate and list all the dates you want to report on. Then to create the report you want, you make a LEFT JOIN query from ReportDates to your datasource. You would show all values from ReportDates and just the matching ones in your other datasource. Doing that will allow you to populate all the dates you want to report on, even if your main data source doesn't have those dates in it.

 

[galaxy]

I've seen that solution on other forums, but it doesn't give me any results because there is no exact match between StartTime and a table of dates. I would have to generate a table with every possible time in order to get matches. Even if I did that, the giant list of times that had no match would be completely useless.

I tried to create an expression that would format the StartTime as just the day and month to match the table, but when I perform a LEFT JOIN, I get a type mismatch error. The data type of tbl_dates.MyDate is Date/Time and the Format is dd-mmm. I feel like I'm close, can anyone see what I'm doing wrong?

Here's qry_dates

SELECT (Format([StartTime],"dd-mmm")) AS DayMonth, [tbl_projects].Project
FROM [tbl_projects]
ORDER BY (Format([StartTime],"dd-mmm"));

And here's the join query that gives the type mismatch error

SELECT qry_dates.Project, tbl_Date.MyDate
FROM tbl_Date LEFT JOIN qry_dates ON tbl_Date.MyDate = qry_dates.DayMonth;

 

[plog]

Try the DateValue function (http://www.techonthenet.com/access/functions/date/datevalue.php) in your first query:

SELECT (DateValue([StartTime]) AS DayMonth, [tbl_projects].Project
FROM [tbl_projects];

 

[galaxy]

If we're ever in the same location, I owe you a beer! (Or beverage of your choice.)