Date Range

[citloner]

I have created a query based on one table to search for dates in a certain
range, for example May, by searching the date field with the criteria Between
[enter start date] And [enter end date]

It pulls up most records but always leaves excludes a few records which
should be included. Previously all dates were in a different format, so I
changed them all to d/m/yy. The date field in the table is text. Should it
be changed to a date format? If this is an issue, why is it pulling up most
of the fields?

Thanks.

 

[John Spencer]

Could you post the SQL you are using (or at least the criteria)? Also tell
us if the field is a DateTime field or a text field?

If it is a date field, you could be dropping records at the end of the
period if your field contains a time other than midnight. This often
happens if the date field value is being set using the NOW() function
(includes date and time) versus using the Date() function ( only the date -
well it includes the time of midnight 00:00:00).

If your field does contain a time you may need to change the criteria you
are using to something like

= #05/01/2007# and < #06/01/2007#

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have created a query based on one table to search for dates in a certain
range, for example May, by searching the date field with the criteria
Between
[enter start date] And [enter end date]

It pulls up most records but always leaves excludes a few records which
should be included. Previously all dates were in a different format, so I
changed them all to d/m/yy. The date field in the table is text. Should
it
be changed to a date format? If this is an issue, why is it pulling up
most
of the fields?

Thanks.

 

[citloner]

The date is a text field. There is no reference to time.

The criteria in the query date field is: Between [enter start date] And
[enter end date]

I changed the field to a date/time field and now the query doesn't pull
anything up. I changed it back to a text field and I get some but not all
data.

Could you post the SQL you are using (or at least the criteria)? Also tell
us if the field is a DateTime field or a text field?

If it is a date field, you could be dropping records at the end of the
period if your field contains a time other than midnight. This often
happens if the date field value is being set using the NOW() function
(includes date and time) versus using the Date() function ( only the date -
well it includes the time of midnight 00:00:00).

If your field does contain a time you may need to change the criteria you
are using to something like

= #05/01/2007# and < #06/01/2007#

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have created a query based on one table to search for dates in a certain
range, for example May, by searching the date field with the criteria
Between
[enter start date] And [enter end date]

It pulls up most records but always leaves excludes a few records which
should be included. Previously all dates were in a different format, so I
changed them all to d/m/yy. The date field in the table is text. Should
it
be changed to a date format? If this is an issue, why is it pulling up
most
of the fields?

Thanks.

 

[John Spencer]

I am going to guess that you didn't declare the parameter types. If so
then Access may be misunderstanding and treating the date entry as a
math equation. 1/1/2008 is 1 divided by 1 divided by 2008 which is a
date time very early in the morning of Dec 30, 1899.

ON a DATE field try

Between CDate([enter start date]) And CDate([enter end date])

Or at the beginning of the query add

Parameters [enter start date] DateTime,
[enter end date] DateTime;
SELECT ...
FROM ...
WHERE TheDateField Between [enter start date] And [enter end date]

You can do that in the design view (query grid)

--Open the query in design mode
--Select Query: Parameters from the Menu
--Fill in the EXACT name of the parameter in column 1
--Select the data type of the parameter in column 2