That works! I needed to modify the SQL to recognize the dates (good 'ole #)
Here's what it ended up being:
Dim strSQL2 as String = "SELECT Sum([LoadCount]*[AvgCYLoad]) AS SumOfTotal " & _
"FROM tblMain INNER JOIN tblSub ON tblMain.ID = tblSub.ID " & _
"WHERE (((tblMain.Date)>=#" & TextDay1.Text & "# And (tblMain.Date)<=#" & TextDay2.Text & "#) AND ((tblSub.LoadCount)>0));"
You guys are great, always a big help!