IIf not working

[jbphoenix]

Here is some sample data
ProductLineTransAmountTransDateCustomerNameGL$10.0020080715DATSB$15.0020080715DATSA$20.0020080715DATSH$25.0020080715DAOTH$30.0020080715DA

Here is a copy of my statement -
22: IIf([ProductLine]="GL" Or "MGL" Or "SBL",[TransAmount],0)

Now here is the result
ProductLineTransAmountTransDateCustomerName22GL$10.0020080715DA$10.00TSB$15.0020080715DA$15.00TSA$20.0020080715DA$20.00TSH$25.0020080715DA$25.00OTH$30.0020080715DA$30.00
I can't figure out why there is data in 22 for productline's other than GL. Only GL should have a dollar amount. Any idea's?

 

[pbaldy]

The correct syntax would be

22: IIf([ProductLine]="GL" Or [ProductLine]="MGL" Or [ProductLine]="SBL",[TransAmount],0)

 

[jbphoenix]

Thanks - I had brain fart.

 

[georgedwilkinson]

You're asking your Iif to evaluate a couple of True/False criteria. One of those criteria is: "is the field [ProductLine] = "GL"?". Another of the criteria is: "is "MGL" true?" The third is: "is "SBL" true?"

A couple of concerns. Is there more than one object in the database named [ProductLine] (i.e. a field on this form and a column in the table/query this form is based on)? If there is, fix it.

The other concern is creating a valid Iif. You need to compare [ProductLine] to each of the 3 criteria you have provided.

Not sure if Iif allows the use of the "in" operator but (if it does) one way might be:
IIf([ProductLine] in ("GL", "MGL", "SBL"),[TransAmount],0)

The samples you've given here are useless...all runs on 1 line and doesn't seem to be a customer name in the data, I guess.