Max Date

[Tom]

I have a table w/ a few hundred records. The table contains various fields
plus a date field.

I need to create a query that retrieves all records and puts FALSE into all
records (date field) except for the single record that has the most recent
date.

So, the record w/ the most recent date (MAX date) should have a TRUE in the
date field?

How do I do that?

 

[fredg]

I have a table w/ a few hundred records. The table contains various fields
plus a date field.

I need to create a query that retrieves all records and puts FALSE into all
records (date field) except for the single record that has the most recent
date.

So, the record w/ the most recent date (MAX date) should have a TRUE in the
date field?

How do I do that?

In a query, what you can do is create a new column:
Exp: IIf([ADate]=DMax("[ADate]","TableName"),"True","False")

but this is not a date field. You can not write true or false in a
date datatype field.

 

[Tom]

Fred,

that works perfectly!!! Thanks so much for sharing this w/ me.

--
Tom

I have a table w/ a few hundred records. The table contains various
fields
plus a date field.

I need to create a query that retrieves all records and puts FALSE into
all
records (date field) except for the single record that has the most
recent
date.

So, the record w/ the most recent date (MAX date) should have a TRUE in
the
date field?

How do I do that?

In a query, what you can do is create a new column:
Exp: IIf([ADate]=DMax("[ADate]","TableName"),"True","False")

but this is not a date field. You can not write true or false in a
date datatype field.

 

[Duane Hookom]

SELECT *, [DateField] = (Select Max([DateField]) FROM tblA) as TrueFalse
FROM tblA;

This should return a TrueFalse column with either -1 or 0.