Between And Operator in Date Field

  • Thread starter Thread starter Ashbee
  • Start date Start date
A

Ashbee

I have a query based on the following fields of a data
table - ID PART DESCRIPTION DATE

In the criteria part of the DATE field I have inserted a
Between And statement using User entered parameters for
start and end dates.

When requesting data return for the fiscal year July 2003
to Jun 2004 I am getting odd data from the years 2000,
2001, 2002. I have randomly checked original data using
the ID's but can't detect any error in the DATE field that
might lead to this error. I have rechecked spelling for
fields but no obvious problems there.

I have used User entered paramaters for DATE in other
queries on the same database without error.

I would be grateful if anyone might be able to point me in
the right direction to ensure my query operates as it
should.
 
Several possible issues:

1. If you open the table in design view, what is the Field Type of your
field named "Date"? Is it Date/Time? Text? We will work on the assumption
that it is Date/Time. If not, fix it.

2. In the query where you limit the data to the fiscal year, I assume you
have something like:
Between [StartDate] And [EndDate]
Declare these parameters so that the query knows they are date/time values.
Choose Parameters from the Query menu. In the dialog enter 2 rows:
[StartDate] Date/Time
[EndDate] Date/Time

3, When you run the query, you will need to enter full dates (not just
months) into the parameter boxes, e.g.:
7/1/2003
and
6/30/2004

4. That should work unless the date/time field actually contains a time
component in your table. If it does - typically because you used =Now() as
the Default Value, when =Date() may have been better - you need to change
the Criteria at step2 to:
= [StartDate] And < ([EndDate] + 1)
Click to expand...
 
Many thanks for your help,

I hadn't set the parameters vaulues from the query menu.
-----Original Message-----
Several possible issues:

1. If you open the table in design view, what is the Field Type of your
field named "Date"? Is it Date/Time? Text? We will work on the assumption
that it is Date/Time. If not, fix it.

2. In the query where you limit the data to the fiscal year, I assume you
have something like:
Between [StartDate] And [EndDate]
Declare these parameters so that the query knows they are date/time values.
Choose Parameters from the Query menu. In the dialog enter 2 rows:
[StartDate] Date/Time
[EndDate] Date/Time

3, When you run the query, you will need to enter full dates (not just
months) into the parameter boxes, e.g.:
7/1/2003
and
6/30/2004

4. That should work unless the date/time field actually contains a time
component in your table. If it does - typically because you used =Now() as
the Default Value, when =Date() may have been better - you need to change
the Criteria at step2 to:
= [StartDate] And < ([EndDate] + 1)
Click to expand...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a query based on the following fields of a data
table - ID PART DESCRIPTION DATE

In the criteria part of the DATE field I have inserted a
Between And statement using User entered parameters for
start and end dates.

When requesting data return for the fiscal year July 2003
to Jun 2004 I am getting odd data from the years 2000,
2001, 2002. I have randomly checked original data using
the ID's but can't detect any error in the DATE field that
might lead to this error. I have rechecked spelling for
fields but no obvious problems there.

I have used User entered paramaters for DATE in other
queries on the same database without error.

I would be grateful if anyone might be able to point me in
the right direction to ensure my query operates as it
should.
Click to expand...


.
Click to expand...
 
Back
Top