Harry,
I don't think it's a matter of overflowing data types. You could easily check this if you set varMinDate and varMaxDate manually to some appropriate numbers.
I see two issues here:
1) Your raw Date are values with nine digits and look like 183104242, and you use
date#(mid(Date,1,8),'YYYYMMDD')
to parse your values into Date type. That works just fine with values like 183104242 (April 24, 1831), but you also have values like 177500002 or even just 0. Both will not be succesfully parsed, because they miss an appropriate format (177500002 would imply Month 0, Day 0, which is invalid, same for just 0).
So you need to decide what to do with these values, how to detect and handle them.
2) You use the peek function to retrieve the first and last CalendarDate record from your Dates table. But the table is unsorted, so you won't necessarily get the min and max CalendarDates in your varMinDate and varMaxDate. Because of 1), some rows won't have a valid CalendarDate, and in your sample the last record shows a NULL. This is why your master calendar won't be built correctly.
If you fix 1) and sort your Dates table by Date, the master table should just work fine.
Hope this helps,
Stefan
edit: One more note: You might encounter some issues in displaying CalendarDates with dates b.c. though.
Here it seems that the date formatting using date() will not display the values correctly.
