Calendar is not showing all dates in table when I add a date field from another table

[iDataDrew]

There is a 1-* relationship that exists between my Date/Calendar table and the Projects table, which contains the ProjectStartDate.  When I add the ProjectStartDate field into a table with the Date field, gaps show up in the Date field, even though dates exist in that field for the ProjectStartDate.  If anything, I would expect gaps in the ProjectStartDate field and, of course, dates should be listed in both columns when there are matches in both fields.  Any ideas on why this is happening?

[Greg_Deckler]

Are you sure you have 2019 dates in your Calendar table?

[iDataDrew]

@Greg_Deckler Yes.  The calendar table goes until 2020.

[Greg_Deckler]

Any way to share your data or your file? Difficult to recreate.

[iDataDrew]

@Greg_Deckler Here's a dropbox link to the file.  Let me know if you have any problems accessing it.

[Greg_Deckler · Accepted solution]

Aha! I think I see your issue. Your dates have start and stop times in them. But, when you create a calendar table, you will get everything as 12:00:00 AM. So, if the times don't match up, no relationship. I'm guessing this is the issue. So, you would want to create a column in each table that is only the date or you would create a column in your Project table that is the date with a time of 12:00:00 AM and use that as the relationship to your Calendar table.

[iDataDrew]

I formatted the dates as Date in report view but not in the Query Editor.  After formatting them as Date in the Query Editor the issue went away.  Thanks for your help!