Why won't my query to show only 2005 data work?

P

Pat Hughes

I have data for 01/01/05 to present. I want to show only the data for
certain months for 2005 only but I keep getting both 2005 and 2006 data
coming up. I tried using "Between 01/01/05 and 02/01/05" with no luck. I
have tried using >01/01/05 and <02/02/05 with no luck.

Thanks,
Pat
 
R

Rick Brandt

Pat Hughes said:
I have data for 01/01/05 to present. I want to show only the data for
certain months for 2005 only but I keep getting both 2005 and 2006 data
coming up. I tried using "Between 01/01/05 and 02/01/05" with no luck. I
have tried using >01/01/05 and <02/02/05 with no luck.
Click to expand...

Dates in Access queries need to be delimited with #
 
P

Pat Hughes

I checked and it is as text. Can I still do a query without changing it to
date?
Thanks.
 
R

Rick Brandt

Pat Hughes said:
I checked and it is as text. Can I still do a query without changing it to
date?
Click to expand...

You can convert to a date on-the-fly just for the purpose of your criteria but
that will be VERY inefficient...

WHERE DateValue(FieldName) BETWEEN #1/1/2005# AND #12/31/2005#
 
A

Amy Blankenship

I think if you use CDate and the hash marks (#) as posted previously it
should work, so

SELECT Thing from tblWhatsit WHERE CDate(txtDateField) < #01/01/2006#

HTH;

Amy
 
G

G. Vaught

For ease of use, I would change your dates to be date/time fields.
Otherwise, just as now, you will have problems getting the results you want
down the road. Make a copy of your database before you change any datatypes.
 
K

KARL DEWEY

What you posted was the criteria and not the SQL statement. To seee the SQL
open the query in design view, click on VIEW - SQL View.

Take what G. Vaught said about canging your datatype to DateTime to heart.

The criteria you used on a text field will pull dates like 10/1/05, 11/1/05,
and 12/1/05. This is be cause it is using text as all of those will be less
than 1/2/05 on an alphabetical sort.
 
P

Pat Hughes

Thank-you to everyone for all of your help. I did change the dates to be
date/time fields. That seems to have worked. I didn't write the program.
It is from IDOT, Illinois Department Of Transportation. I made a copy of the
program and changed the dates to be date/time but not in the IDOT's program
which we use to enter the data and to send the data back to IDOT. Should I
just use the copy of the program to do the queries the Deputy Chief of Police
wants? This way I would have to copy all new data into the copied program
and change the dates to date/time types. Will the data transfer correctly if
the original has the dates in as text.

Thanks
Pat
 
Top