>Hi John
>
>Actually, the SQL you wrote will give the highest datefield in 1997 and the other columns will come from the last physical record in 1997. This command will only work correctly if the record with the highest datefield is the last physical record entered in 1997.
>
>If an earlier record is editted and the date is changed to be the highest date, the remaining columns will not be from this record.
>
>>Hey Steve,
>>
>>select max(datefield),* from mytable where year(datefield)=1997
>>
>>
>>This will return the row representing the highest date in 1997.
>>
>>>I have a table full of records with a date field in each record. The date field is D 8. For example I see 02/07/1999 when I browse the table. Of course there are other fields in the record, but the date field is the one I need to use to find the last record for a given year.
>>>
>>>My table has the following records with dates in them (the actual table has about 4000 records in it, but this is an example to illustrate what I am trying to accomplish -
>>>MyTable Date Field (D 8)
>>>03/06/1995
>>>03/07/1995
>>>01/04/1996
>>>07/04/1996
>>>09/22/1996
>>>02/22/1997
>>>08/15/1997
>>>11/27/1997
>>>12/22/1997
>>>04/07/1998
>>>05/19/1998
>>>and so on ...
>>>
>>>Can I locate the last record for 1997 in fewer steps? Right now, I have a SCAN ... ENDSCAN with a DO WHILE ... ENDDO running inside of that to locate the last record in a given year. It seems like there should be a way to go to the last record for a given year in a single command? I think I am going about this the long way.
>>>
>>>Help please and thank you.
and since he didn't specify whether or not there might be more than one record with the same date, and to access all fields of correct record, you might do:
Desired_Year = 1997
SELECT MAX(datefield) AS Max_Value FROM MyTable WHERE Year(datefield) = Desired_Year
Max_Datefield = Max_Value
USE
SELECT COUNT(*) AS Num_Selected,* FROM MyTable WHERE datefield = Max_Datefield
IF ( Num_Selected > 1 )
ENDIF
patrick