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