Whenever I get a bit of SQL code that doesn't work I put it into a variable, e.g.
mySQL = "SELECT visits.* FROM visits WHERE visits.visitdate BETWEEN #" & Me.startdate.Value & "# AND #" & Me.EndDate.Value & "# AND visits.repname='Peter';"
Put a break in the code, use the debug or immediate window to see the code, (print mySQL). highlight it and copy it. Open a new query in SQL view. Past the code and run it. Very often the error is highlighted with a better message than you get in the code.