Calculated field - Access 2003

[Guest]

My table has the following fields
UniqueID AutoNumber Primary key
Title - Text
TitleID - Number
TitleDate Date/time
MembershipID - Number

I have a calculated field in my form named:
CurrentTitle

Objective
I want to lookup my table for the most current Title date for each member
and return the corresponding title to the CurrentTitle field.

Thanking as always for your assistance.

 

[Wayne Morgan]

If you make the control a listbox sized one row high, try this for the Row
Source.

SELECT TOP 1 Title FROM TableName WHERE MembershipID=[txtMembershipID] ORDER
BY TitleDate DESC;

where [txtmembershipID] is a textbox on the form and contains the value of
the MembershipID for the person you're looking up. If there is more than one
title on that date, then you will get more than one item returned.

 

[Larry Daugherty]

Look in Access help for TOP. You'd want TOP 1 with your query ordered
by TitleDate.

HTH