iif statement comparing two fields

[unslog]

The two tables are joined together by a primary and foreign key in the query, . I have not added the actual tables below , as data is senitive - i'm using an example. This join is made using the graphic relationship between the tables.

Table_1 with below column
Mode


6
6
6
6
6
4
4

Primary KEY
0001
0002
0003


Table_2 with below column

Type
06
GL
PL
04
16


Foreign KEY
0001
0002
0003



Initially the pseudo code i'm trying to get to work is;

if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH"


Expr1: IIf(IsNumeric([TABLE_2]![TYPE])="06" And IsNumeric([TABLE_1]![MODE])=6,"MATCH","NO_MATCH") ,

However this calculated field returns incorrect results, i.e. the query returns MATCH for rows that do not match , e.g. mode=6 and type=GL , Previously , when it was returning #Error when trying to match mode=6 and type=GL when it should ideally return "NO_MATCH", which is why I added Isnumeric.

Once I have sorted this out, I would like to achieve this as the calculated field


if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH" or if table_1.mode = 4 and table_2.type = 04 , then "MATCH" , else "NO_MATCH

Any ideas how I can do this?

Thanks

[JoeM]

I am a bit confused as to what you are trying to accomplish. Your example is a bit tough to follow too, because you show thr Primary Key and Foreign Key values separate of the Mode and Type fields within the same tables. So we really don't have a good picture of sample data.

Maybe if you post sample data tables (with just the pertinent fields), and your expected output, it will be clearer.

[unslog]

I'm trying to create an exception report from an ODBC connection

This report needs to list jobs where certain information is missing or incomplete, which is why the SQL query is a bit long. The other "if statements" are working fine (i think) .

The part i'm having difficulties with is comparing to fields if they match i.e. each job that appears in the query result should have;

Transport Mode set to 6 and contact type set to 06 . if these fields match in a job row , then the calculated result should be "OK" for this row.

otherwise return "Missing contact"

in addition to the above, each job that has transport mode set to 4, should have a corresponding contact type 04

that's it!

Here is a screen shot of the current output while ONLY trying to match transport mode 6 with Contact type 06. Once This is working I wanted to bolt on the other condition into the calculated field i.e. or if transport mode = 4 and contact type = 04 then also OK, other missing contact 04



Please ignore references to the Analysis code table, as yet to bring that table into the matter.

Hope I have clearly explained what i'm trying to do

[JoeM]

Maybe this is simple than I thought. How about something like this?

check_6: IIf(Format([Transport Mode],"00")=[Contact Type],"OK","missing contact type 6")

[June7]

Is 6 the only value in TransportationMode field? Or should the message NOT have reference to 'type 6'?

[RayMilhon]

Your Statement: Expr1: IIf(IsNumeric([TABLE_2]![TYPE])="06" And IsNumeric([TABLE_1]![MODE])=6,"MATCH","NO_MATCH")

Will Always Evaluate to NO_MATCH because IsNumeric([table_2].[type]) Returns a boolean value so it will return True or False Never "06" what you want is Val("06")

[JoeM]

what you want is Val("06")

...or convert the other one to text using the FORMAT function, like I did.

Basically, you want to compare "number-to-number" or "text-to-text".