How can I create an expression in a query that looks to see if a value exists in another field?

[cliff.clayman]

I have two fields in two separate tables. Table1 has a field named Field1 and Table2 has a field named Field2.



The values of Field1 are like this:

IL Terr 001
NY Terr 002
TX Terr 003

The values of Field2 are like this:

Territory 001
Territory 002
Territory 003

I am trying to compare the two fields to see if the 001, 002, 003 values are in both fields. How can I do that?

[ssanfu]

Use separate fields.
You would need at least 3 fields in table1 and at least 2 fields in table 2. ("Terr")

I would have
Table1
-------
State (text) ex. data: IL, NY, Tx
Terr (text) ex. data: 001, 002, 003


Table2
--------
Terr (text) ex data: 001, 002, 003


Having the state and the territory number in one field violates the 1NF rule (IIRC)

[cliff.clayman]

I cannot make changes to the Tables. These are imports from online reports and I need to be able to compare them as is. I was able to pull out the 001, 002, 003 values from Field1 using:

Field1char3: Right([Field1],3)

However, I don't know how to take that and compare to see if that value is in Field2.

[ssanfu]

OK, got it.

Are the two tables related? (a record in table1 is related to one or more records in table2)
Or does it matter? Are you just wanting to know if there is a record with "001" in table1 AND a record with "001" in table2?

[cliff.clayman]

There is a linking field for the tables. So, basically when Table1.Field1 = IL Terr 001 and Table2.Field2 = Territory 001, I want TRUE or 0 or whatever and when Table1.Field1 = NY Terr 002 and Table2.Field2 = Territory 001, I want FALSE or 1 or whatever returned so I can explicitly only return the FALSE matches.

I did this and it "worked", but it returns 11 for TRUE and 0 for FALSE. Is there a better way to write this?

InStr(1,IIf(IsNull([Field2]),"000",[Field2]),Right([Field1],3),1)

[ssanfu]

Not sure I totally understand the table structure, but try this:

Create a new query
Add Table1 and Table2
Link the tables on the linking field
From Table1, drag down Field1
From Table2, drag down Field2
In the next empty column, enter (copy and paste)

Match: (Right(IsNull([Field1],"000"),3)) = (Right(IsNull([Field2],"000"),3))

Run the query.


The records for the 3rd column should be either TRUE / FALSE (or -1 (true) / 0 (false))

[cliff.clayman]

I receive an error:

The expression you entered has a function containing the wrong number of arguments.

Match: Right([Field1],3)=Right(IIf(IsNull([Field2]),"Territory 000",[Field2]),3)

The above code works! Thanks for all the help!!

[ssanfu]

Great!
Ready to make this solved?