Changing values

[Guest]

I've racked my brain over this one.

I have two fields. One is BOL and the other is RAD Date. BOL's can have
multiple RAD dates. I need either a way for my query to choose the earliert
one listed or I need an update query to change the values in my table. Is
this possible? For instance:

BOL RAD Date Customer
12345 7/16/06 XYZ Corp
12345 7/14/06 XYZ Corp
12345 7/22/06 XYZ Corp

I would need my query to pull only one line of data which has the RAD date
of 7/14/06.

 

[John Vinson]

I've racked my brain over this one.

I have two fields. One is BOL and the other is RAD Date. BOL's can have
multiple RAD dates. I need either a way for my query to choose the earliert
one listed or I need an update query to change the values in my table. Is
this possible? For instance:

BOL RAD Date Customer
12345 7/16/06 XYZ Corp
12345 7/14/06 XYZ Corp
12345 7/22/06 XYZ Corp

I would need my query to pull only one line of data which has the RAD date
of 7/14/06.

A Query with a criterion

=(SELECT Min([RAD Date]) FROM [yourtable] AS X
WHERE X.[BOL] = [yourtable].[BOL])

will filter the query to return only the earliest date.

If you need the query to be updateable, use instead

=DMin("[RAD Date]", "[yourtable]", "[BOL] = " & [BOL])

John W. Vinson[MVP]