Filtering a Lookup Field on Two Criteria

[p2p]

I'm trying to create a lookup field that retrieves meeting dates for meetings
held with clients and presents them for multiple selection. I want to limit
the dates that appear in the combo box to dates of meetings held with the
client that did the trade.

I have a Meetings table and a Trades table. The Meetings table stores
details about each meeting (attendees, topics discussed, date, client, etc.).
I want to link trades that result from a meeting with that meeting. So, in
the Trades table, I have ClientID and MeetingDates columns. ClientID is a
lookup for the client that stores the client's name in the field. Next is a
MeetingID column with a combo box. Clicking on the down arrow should reveal
dates from the MeetingDate field of the Meetings table, filtered by client,
WHERE Trades.ClientID=Meetings.ClientID.

The best I've been able to come up with is:

SELECT Meetings.MeetingDate
FROM Meetings INNER JOIN Trades ON Meetings.ID = Trades.[Meeting Date].Value
WHERE (((Meetings.ClientID)=([Trades].[ClientID])))
ORDER BY Meetings.MeetingDate;


but this isn't working. Is it possible to use a field in a record to filter
the lookup results for another field in the same record? How can I do this?


Thanks.

Brad

 

[Jeff Boyce]

Brad

From your description "... a lookup field...", it sounds like you are trying
to do this directly in a table. Bad idea!

First off, you can only use a single field for a lookup datatype.

Second, and more importantly, what the table displays will NOT be what it
stores ... and this causes considerable confusion.

The preferred method is to use two (foreign key) fields for two related
tables, and store the value of the related table's row's primary key. Then,
to get a "lookup" function, use forms. Access tables store data, Access
forms (and records) display data.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP