Avatar of SteveL13
SteveL13
Flag 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?
Microsoft Access

Avatar of undefined
Last Comment
PatHartman

8/22/2022 - Mon
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

SteveL13

ASKER
Where do I put this code?  I tried in the on current event of the form but that doesn't work.
Phillip Burton

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
SteveL13

ASKER
But the record source of the form is a table.
Phillip Burton

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
Phillip Burton

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.