Also, remember that a date field in Access is actually a double, containing the day number and the decimal portion of the day. 1.45 would represent 10:48:00 AM on December 31, 1899. (Since the zero date is 12:00:00 AM on Dec 30, 1899.) If you don't specify a time, Access assumes midnight of the date in question.
However, this also implies that you are looking for a date that falls between two identical dates at midnight. I would suggest formatting to include start and end times as well.
in other words,
select Field1, Field2 from Table1 Where DateField Between #1/2/2006 00:00:00# And #1/2/2006 23:59:59#
(actually, the 00:00:00 isn't needed, as it is assumed...)
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert