Link to home
Start Free TrialLog in
Avatar of SteveL13
SteveL13Flag for United States of America

asked on

Identify match in two tables

I have two tables.  In one of them there is a field named "Property ID".  In the other one there is a field named "Account No".   Both field are text fields.

Also I have a form bound to the 1st table.  If there is a matching value in the 2nd table ("Property ID" = "Account ID") then I want a label to appear on the form indicating that there is a match.

How can I do this?
Avatar of Phillip Burton
Phillip Burton

Change "I have a form bound to the 1st table" to "I have a form bound on a query based on the 1st table", and that query can be:

SELECT Table1.*, IIf(IsNull([Table2].[Account Number]),"","Match") AS MyLabel
FROM Table1 LEFT JOIN Table2 ON Table1.PropertyID = Table2.[Account Number];

Open in new window

Avatar of SteveL13

ASKER

Where do I put this code?  I tried in the on current event of the form but that doesn't work.
In the "Record Source".

You will need to modify Table1 and Table2 names. MyLabel is the new field which will say "Match" if there is a match, and nothing if there isn't.
But the record source of the form is a table.
That's why I said:

Change "I have a form bound to the 1st table" to "I have a form bound on a query based on the 1st table"

It's simplest to add another column to the 1st table.
ASKER CERTIFIED SOLUTION
Avatar of Phillip Burton
Phillip Burton

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of PatHartman
As an aside - the recordsources for forms and reports should almost always be queries.  That allows you to:
1. Limit the rows/columns fetched from the database by using a where clause
2. Avoid the use of domain functions to "look up" values
3. Provide a permanent sort order for the recordset so rows are ordered in a predictable sequence