To run date comparisons from a Query use date in this format #mm/dd/yyyy#
e.g.
Select * from table where date_Column between #07/01/2010# and #08/01/2010#
To do date comparison within VB use datavalue in the Insert and/or Select statement:-
strSQL = “INSERT INTO table_name1″ _
& ” SELECT * ” _
& ” FROM table_name2″ _
& ” WHERE ([Date_Field] between ” _
& ” datevalue(‘” & strStartdate & “‘)” _
& ” AND datevalue(‘” & strEnddate & “‘))”
DoCmd.RunSQL strSQL