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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 #
 
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#
 
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
 
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.
 
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.
 
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
 
Back
Top