Need to query for missing dates

[Jaron]

Hi. I need to run a query to return a list of dates for which there are no records. From suggestions on the internet, I've created a separate table containing one field called "Record_Date" with the range of dates that I need to compare with ("DateRangeTable").


The table I'm searching has 14 fields, but one of them is also named "Record_Date", the same as the field in the date range table. The SQL I've got is:

SELECT DISTINCT DateRangeTable.Record_Date
FROM DateRangeTable, WeeklyTimesheetQueryResults
WHERE (((DateRangeTable.Record_Date)<>[WeeklyTimesheetQueryResults].[Record_Date]));

However, this query returns all the dates listed in the date range table, even if there is a record with a matching date in the recordset table. My only guess is the date formatting. Both "Record_Date" fields in both tables are of the "Date/Time" Data Type, but for some reason in the record-set the field shows only the date, whereas in the DateRangeTable it shows both date and time. Even if I go to design view and format as short date, if I click on the field it changes to show the time as well, so I'm assuming that regardless of formatting, the base data still holds the date and time. But the record-set table doesn't behave the same way. I'm not sure if my data entry form is not putting a date/time in there, or what might be the problem; since it loads it from a datepicker, I would assume that the time comes with the date automatically.

If I can't change the record-set table to match the DateRangeTable (by adding the time to the date), my next solution would be to go the other way around, that is, change the DateRangeTable to match the record-set table, by removing the time from the date (since all I really need here is the date, and I was planning on stripping it out with report formatting anyway).

To do that, I'd have to know how to change some SQL that is called from a module, which is behind a form with a textbox, also with a datepicker:

DoCmd.RunSQL "INSERT INTO DateRangeTable ([Record_Date])" & "VALUES(#" & "" & Me!WE_Date - 6 & "#);"
DoCmd.RunSQL "INSERT INTO DateRangeTable ([Record_Date])" & "VALUES(#" & "" & Me!WE_Date - 5 & "#);"
DoCmd.RunSQL "INSERT INTO DateRangeTable ([Record_Date])" & "VALUES(#" & "" & Me!WE_Date - 4 & "#);"
DoCmd.RunSQL "INSERT INTO DateRangeTable ([Record_Date])" & "VALUES(#" & "" & Me!WE_Date - 3 & "#);"
DoCmd.RunSQL "INSERT INTO DateRangeTable ([Record_Date])" & "VALUES(#" & "" & Me!WE_Date - 2 & "#);"
DoCmd.RunSQL "INSERT INTO DateRangeTable ([Record_Date])" & "VALUES(#" & "" & Me!WE_Date - 1 & "#);"
DoCmd.RunSQL "INSERT INTO DateRangeTable ([Record_Date])" & "VALUES(#" & "" & Me!WE_Date & "#);"

Basically, it takes the date I enter into the textbox (Week ending date, or "WE_Date") and extrapolates the dates for the week, then enters them into the table. Is there some tweaking I need to do here? Again, this is all from the wild guess that my Record_Date fields are not matching, because for some reason my Unmatched query isn't catching anything.

Where should I start?
Thanks for any suggestions.

[June7]

Did you try the Find Unmatched query wizard? Return all records from DateRangeTable that don't have a match in WeeklyTimesheetQueryResults.

[Jaron]

Yes, with the wizard I came up with:

SELECT DateRangeTable.Record_Date
FROM DateRangeTable LEFT JOIN WeeklyTimesheetQueryResults ON DateRangeTable.[Record_Date] = WeeklyTimesheetQueryResults.[Record_Date]
WHERE (((WeeklyTimesheetQueryResults.Record_Date) Is Null));

But this produces all of the entries in the DateRangeColumn, although there are matches in the other table. Am I misunderstanding the operation of an Unmatched query? Is the comparison table supposed to have all the same fields and data as the recordset table?

<EDIT> Nope. I just experimented by giving my DateRangeTable all the same fields as the recordset table. I even put the same data in one row, so there would be no hang-ups there, although I am reasonable sure that the query only looks at the field you tell it to match. But it did not find any matches and returned all results from the DateRangeTable. I think this tells me that there really is a discrepancy between the data in the Record_Date fields of both tables.

<EDIT2> Yup. I think the problem is the data. I just looked at the form that enters the data into the recordset table again and compared it with the other form that supplies the search date (WE_Date), and when I choose a date with the datepicker, it enters only the date, whereas the other form inputs the date and time. The textboxes on both forms are set to 'General Date', and the data type on both tables being compared in the query is set to 'Date/Time'. Does anyone know why the datepicker would do two different things?

[June7]

Yes, the query syntax looks right so the results are mysterious. If you want to provide data for analysis, follow instructions at bottom of my post.

[Jaron]

Ok. Well, before I strip it down and upload it I'm going to try a few more things. Thanks for verifying the syntax though.

[Jaron]

There it is! The form that supplies the WE_Date, from which my code generates the DateRangeTable field, has an on load event that sets the default value to last Sunday. The code that does that is

Option Compare Database
'Function returns date of Sunday preceeding today's date
Function LastSunday() As Date
Dim bToday As Byte, intDayDif As Integer
 bToday = DatePart("w", Now) 
 intDayDif = (bToday - vbSunday) '+ 7 '(Uncomment to find the previous Sunday's date instead of last Sunday)
 LastSunday = DateAdd("d", -(intDayDif), Now)
 End Function

In lines 5 and 7, where it says "Now", I changed it to "Date", and now it now evaluates last Sunday to the date only, not the time. This sets the default value of the form textbox to a date without the time, and when it generates the DateRangeTable, it is a list of all the dates of last week without the time. Now the query recognizes the matches.

Not sure how I found that, but I'm sure coffee had something to do with it.