Thanks Duane - i'm at least now getting SOME records showing up
my date format (as i'm in Australia) in my database is dd/mm/yyyy so i entered
in Between 27/05/2009 and 30/06/2009 - but the filters results are still
showing
dates for 2007 and 2008
The dates in an Access Date/Time field are actually stored as a number, a
double float count of days and fractions of a day since midnight, December 30,
1899. The format merely controls the appearance - your dates AREN'T stored as
dd/mm/yyyy or in any other specific format!
However, date *literals* enclosed in # marks must be either in American
mm/dd/yy (or yyyy) format - the Access programmers were, after all, Americans;
or an unambiguous format such as the ISO yyyy-mm-dd format.
Try a search criterion of
= Format(CDate([Forms]![YourForm]![txtStartDate]),"mm\/dd\/yyyy") AND < DateAdd("d", 1, CDate([Forms]![YourForm]![txtEndDate]), "mm\/dd\/yyyy"))
The DateAdd caters to the possibility that your datefield contains a time
portion, and will find date values through to the end of the date specified in
the criterion.
You can of course use date literals instead, but they must be in one of the
formats above.