Date (and time) constants in Access are *ALWAYS* specified by surrounding the values with #...#.
Access is the only DB to user #...#. All others indeed use '...' as you show, though often with strict requirements of the format of the date within the apostrophes.
Also, since DATE is a keyword in Access (meaning "today's date"), when you use it as the name of a field, as you have, you need to enclose it in [...] to tell Access that it is *NOT* a keyword.
The other mistake you have is that you have a space between each ' and the succeeding or preceding " mark. Nope. No spaces allowed.
Finally, you probably really should validate those textbox values to be sure they *ARE* dates.
dta = CDate(TextBox1.Text) ' start date
dtb = CDate(TextBox2.Text) ' end date
rs.Open "select * from Table1 where [date] between #" & dta & "# and #" & dtb & "#", con
A bit of caution here: If your [date] field actually holds a date *and* time, then you might not get the results you expect. For example, if you have
WHERE [date] BETWEEN #12/1/2008# AND #12/5/2008#
and your [date] field contains #12/5/2008 3:15:22 PM#, then it will *NOT* be BETWEEN the values, because a date without a time is implicitly a time of 0:00:00 AM.
So if you want to be SUPER safe, you could do:
dta = DateValue(CDate(TextBox1.Text)) ' start date
dtb = DateValue(CDate(TextBox2.Text)) ' end date
rs.Open "select * from Table1 where DateValue([date]) between #" & dta & "# and #" & dtb & "#", con
The DATEVALUE() function strips the time off of any date-and-time value that it is given and gives back just the date.