Date query - simple

K

Ken Smedley

I have used a simple query for years to sort records by by date:
#mm/dd/yyyy#. I've reloaded Access 2000 and the existing DB on a new
Click to expand...
computer and now it won't sort any 2006 records. Still works fine on 2005
records. Records are imported from excel.
 
J

Jerry Whittle

Please show us the entire SQL for the query. Also verify that the dates are
in an actual Date/Time field. Lastly make sure that there are records with
dates for 2006 in the table. If you are displaying only the 2-digit year, you
really don't know if they are 2006, 1906, or some other century.
 
K

Ken Smedley

I'm a novice at this...
SELECT Permits.CODE, Permits.[JOB CITY], Permits.[JOB ADD], Permits.[JOB
ZIP], Permits.OWNER, Permits.[OWN ADD], Permits.[OWN TEL], Permits.
CONTRACTOR, Permits.[CNTR TEL], Permits.ARCHITECT, Permits.[CONTACT ADD],
Permits.VALUE, Permits.[RPT DT], Permits.DATE, Permits.STATUS
FROM Permits INNER JOIN [Ventura 2005 zip codes] ON Permits.[JOB ZIP] =
[Ventura 2005 zip codes].[Zip Code]
WHERE (((Permits.CODE)="SFD") AND ((Permits.[JOB ZIP])=[Ventura 2005 zip
codes]![Zip Code]) AND ((Permits.[RPT DT])>#1/1/2005#) AND ((Permits.STATUS)
="PERMIT"));

Everything has remained the same but for occasional changes to the RPT DT
which is modified at times.

Ken
 
J

John Vinson

I'm a novice at this...
SELECT Permits.CODE, Permits.[JOB CITY], Permits.[JOB ADD], Permits.[JOB
ZIP], Permits.OWNER, Permits.[OWN ADD], Permits.[OWN TEL], Permits.
CONTRACTOR, Permits.[CNTR TEL], Permits.ARCHITECT, Permits.[CONTACT ADD],
Permits.VALUE, Permits.[RPT DT], Permits.DATE, Permits.STATUS
FROM Permits INNER JOIN [Ventura 2005 zip codes] ON Permits.[JOB ZIP] =
[Ventura 2005 zip codes].[Zip Code]
WHERE (((Permits.CODE)="SFD") AND ((Permits.[JOB ZIP])=[Ventura 2005 zip
codes]![Zip Code]) AND ((Permits.[RPT DT])>#1/1/2005#) AND ((Permits.STATUS)
="PERMIT"));

Everything has remained the same but for occasional changes to the RPT DT
which is modified at times.

Ken
Click to expand...

What makes me suspicious here is that you're joining to a table named
[Ventura 2005 zip codes]. Might this table be restricting which
records are being displayed?

You can avoid having to keep changing the RPT DT criterion by using
= DateSerial(Year(Date()), 1, 1)
Click to expand...

to get just the current year's data.

John W. Vinson[MVP]
 
Top