query on date 1

[oaklandar]

In my Access 2000 database I need to pull up all records that have expired based on a field called terminate_date.



So if I have terminate_date values of:
id terminate_date
1 8/15/04
2 10/13/04
3 6/1/04
4 9/20/04


Based on today date of 9/15/04:
it would pull up 8/15/04 and 6/1/04.

Is this the right way??

select * from mytable where terminate_date < now();

 

[lespaul]

It may work if you use date (which only returns the date) instead of now (which returns the date and time):

select * from mytable where terminate_date < date();

Leslie

 

[oaklandar]

Thanks that works. One final issue is how can I also query all jobs where I show all jobs where actual_complete_date is past the estimated complete date?

So if I have:
id estimated_complete_date actual_complete_date
1 8/15/04 9/15/04
2 10/13/04 9/10/04
3 6/1/04 4/5/04
4 9/20/04 10/1/04

It would pull up:
1 8/15/04 9/15/04
4 9/20/04 10/1/04

 

[lespaul]

replace:

both instances of 'myTable' with your table name
both instances of PK with the primary key field of the table.

select * from mytable t1 inner join mytable t2 on t1.PK = t2.PK where t1.actual_complete_date > t2.estimated_complete_date

Leslie

 

[oaklandar]

Thanks

 

[PHV]

Why not simply this ?
SELECT * FROM TableName WHERE actual_complete_date > estimated_complete_date

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244