IIF statement in an Expression 1

[Dauphy]

Help!

I have 2 tables joined into a query.

I only want to show records in a query if one field does not equal another field; both of these fields are from separate tables. I don't know how to go about filtering for records that match this criteria.

Thanks for your help.

 

[r937]

please show us the table layouts, a few sample rows from each table, and which fields you want to compare that aren't equal

rudy

http://r937.com/

SQL Consulting

 

[Dauphy]

OK; I'll shorten it abit.

Table1 = MailingList

Fields: MailingListID
DealerNo

Table 2 = Orders

Fields: MailingListID
OrderDealerNo

Joined Properties: All of MailingList;

Problem (in VB talk):

Show records only if MailingListID!DealerNo is not equal to Orders!OrderDealerNo

Is that clearer???

Thank you...

 

[r937]

still not clear, unless you mean that the mailing list ids must be equal at the same time

here, let me make up some sample rows for you --

[b]MailingList[/b]
MailingListID DealerNo
  23             101
  24             102
  25             103

[b]Orders[/b]
MailingListID OrderDealerNo
  24             109
  25             103
  27             104

what would you like to return from the above?

8 of the possible 9 combinations have dealer numbers that are not equal

rudy

http://r937.com/

SQL Consulting

 

[Dauphy]

Ok thanks ... sorry.... actually I just figured a "workaround", by doing the following expression:

Check: IIf([MailingList]![DealerNo]<>[Orders]![OrderDealerNo],"Y","")

From here I'll do a criteria on the Check field; ="Y".

Of your test data; I wanted only 24 as OrderNo does not equal DealerOrder No.

Thanks again; I'll give you a star for trying to help me...

 

[r937]

select M.MailingListID 
     , M.DealerNo
     , O.OrderDealerNo
  from MailingList as M
inner
  join Orders as O
    on M.MailingListID
     = O.MailingListID 
 where M.DealerNo
    <> O.OrderDealerNo

rudy

http://r937.com/

SQL Consulting