if the beween statement is use as "number between 1 and 7" so record with number 1,2,3,4,5,6,7 will be showing right?? but what if using in date?? example mydate between #04/01/2006# and #04/05/2006# the record i aspect is 04/01/2006, 04/02/2006, 04/03/2006, 04/04/2006, 04/05/2006 but it just showing until 04/04/2006.
The sql statement is show as "SELECT * FROM TBL WHERE mydate BETWEEN #04/01/2006# and #04/05/2006#"

what I think is happening is that internally it is actually between

01/04/2007 and 05/04/2007 ie the month and day are swapped around. I suspect you do not use American date format. Try this
BETWEEN Format(#04/01/2006#,mm/dd/yyyy") and Format(#04/05/2006#",mm/dd/yyyy")

what I think is happening is that internally it is actually between

01/04/2007 and 05/04/2007 ie the month and day are swapped around. I suspect you do not use American date format. Try this
BETWEEN Format(#04/01/2006#,mm/dd/yyyy") and Format(#04/05/2006#",mm/dd/yyyy")

same too the record of 04/05/2006 is not showing.

When [mydate] contains also the times, you can use the DateValue() function on the field to extract the dates for comparison.

See this thread-
http://www.access-programmers.co.uk/forums/showthread.php?t=126851
.