Date Problem

[blackie1982]

Hello All,

I have a table which holds records of work carried out on some equipment. There are two date fields which store the date the work was carried out and the date of the next planned work. Each piece of equipment has many work records. My problem is that I want to select the latest work carried out date and to see if the scheduled work is overdue.

 

[Mile-O]

Use a Totals query and use the Max aggregate on the date field you want.

 

[blackie1982]

Im not sure what you mean by that. Basically here's what I want to do:

1: Get the latest Next Work Due date for each piece of equipment

2: Get the current date

3: Select all the records where Next Work Due date > current date

Thank You.

 

[Mile-O]

1. In the query design view, right click and ensure that the Totals are visible. Select Max for the date field you want the last one of.
2. Use: Date()
3. Put >Date() in the criteria of your date field.

ie..

SELECT Max([MyDate]) AS NextReturnDate
FROM MyTable
WHERE Max([MyDate]) > Date();

 

[blackie1982]

Thank You so much, you made it very very easy!!