Query between dates - help needed

[Robyn]

i was to sort my database to find workers who started between 2 dates

in the filter criteria i tried >=#27/05/2009# and <=#30/06/2009#
(wanting starters between 27/5/09 and 30/6/09 only to be shown after applying
the query)

i get no records showing ... what am i doing wrong with the criteria please?

 

[Duane Hookom]

Try this under your StartDate field:
Between 5/27/09 and 6/30/09

 

[Robyn]

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

appreciate your help
Robyn

 

[Robyn]

thanks Duane

in my database (since i'm in Australia) i used format dd/mm/yyyy so i
entered in Between 27/05/2009 and 30/06/2009 and i did get some
records filtered
however its still including dates in 2008 and 2007
any idea why?

thank you for your help here - its much appreciated

 

[John W. Vinson]

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.

 

[Robyn]

thank you John - but you've lost me completely i'm afraid ...

i tried using date *literals* as in: Between #05/27/2009# and #06/30/2009#
and got no results for the query

i'm fairly new to access and database queries (as you can obviously see by
my ignorance) so you've blinded me with science sorry

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.

 

[Robyn]

thank you both - i have applied both your answers and realised that i had
entered the dates as text not in date/time so my apologies for being dense
and thank you for your help
i have at least learnt a few things today thanks to you both
Robyn

 

[John W. Vinson]

i had
entered the dates as text not in date/time

AHHH! The light dawns. You're right, a Text field will not search
chronologically.

You may want to add a date/time field to your table and run an update query
updating it to CDate([yourtextdate]).