IIF Criteria based on another table

[BED]

This should be an easy question, and quite frankly im pretty annoyed ive spent the ammount of time i have on this problem.



I have a column on Table 1 that is DIV. Ihere are 5 possible values. (ABUS, ABCA, ABMX, BCUS, and BCCA)

Those 5 values have corresponding columns on Table 2.

On Table 2 my goal is for each of the 5 columns to have an if statement that looks and the table 1 value. If it makes i want the criteria for that column to be "True"

ex for table 2 column ABUS criteria.
My if statement in lamens terms is IIF(Table 1 DIV = "ABUS","True","")

My actual if statement is:

IIf([Dealer_DIVCOUNTRY]![DIV]="ABUS","True","")

I am using the "Group By" option and have tried it with Expression, Whereas "Group By"

Let me know what you guys think.

[NTC]

are both tables in the design grid view?...is their a join relationship line between them?

[BED]

EDIT: They are about 5 tables on the design grid they are not directly connected, but are through other tables.

[NTC]

ok; well you only need the table name if there is more than one table with the field name DIV. let's assume there is not. then you need a name for the calculated field...so it should look like this:

FindABUS: IIf([DIV] like "ABUS","True","")

give that a shot....often = does work too....

[BED]

That did not work. =(((

I know a work around that will add about 4 additional queries,but im trying to not go that route.

[weekend00]

do you mean you need to compare each of the 5 fields in table2 with the 5 values? or you want compare field1 with "ABUS" and compare field2 with "ABCA' and so on?

do you really need table1 since it has only one field with 5 records, and you have hard coded these values in you query

[BED]

@weekend00 there are hundreds of records on table 1, and thousands on table 2.

I figured it out using SQL view.

My statement looked like this

((Table1.[Column1])=IIf([Table2]![DIV]="ABUS",True,((([Table1].[Column2?])=IIf([Table2]![DIV]="ABCA",True,"")))))

Etc...

The entire criteria for all 5 columns, is not embeded in one.

Thank you all for your help.

I greatly appreciate it.