Incorrect Syntax

[Divine Mrs. M]

Im am trying to create the following:
If Qual=A, an dif (Transaction Amount/100) is less than highest CEM then
“Eligibleâ€
If Qual=L, and if Transaction Amout is less Qualifier, then “Eligibleâ€
If Qual=M, and if Transaction Amount is less Qualifier, then “Eligibleâ€
If Qual=N, and if (Transaction Amount/100) is less than highet CEM then
“Eligible"
___________________________________________________________________
This is what I built:
Expr2: IIf([Immed Credit]![Qual Code]="A" Or "N" And [Immed Credit]![Total
Tran Amt]/100<[Immed Credit]![Cust 1 CEM] or [Immed Credit]![Cust 2 CEM] or
[Immed Credit]![Cust 3 CEM] or [Immed Credit]![Cust 4 CEM] or [Immed
Credit]![Cust 5 CEM] then "Eligible","yes")
____________________________________________________________________
message returns:
Expression contains invalid syntax
You may have entered and operand without and operator

 

[KARL DEWEY]

Try this --
Expr2: IIf([Immed Credit]![Qual Code]="A" Or [Immed Credit]![Qual Code] "N"
And ([Immed Credit]![Total Tran Amt]/100<[Immed Credit]![Cust 1 CEM] or
[Immed Credit]![Total Tran Amt]/100< [Immed Credit]![Cust 2 CEM] or [Immed
Credit]![Total Tran Amt]/100< [Immed Credit]![Cust 3 CEM] or [Immed
Credit]![Total Tran Amt]/100< [Immed Credit]![Cust 4 CEM] or [Immed
Credit]![Total Tran Amt]/100< [Immed Credit]![Cust 5 CEM] ), "Eligible","yes")

 

[Tom Ellison]

Dear Mrs. M:

From what I've read, it sounds like you want this:

IIf(([Qual Code] IN("A", "N")
AND ([Total Tran Amt] / 100 < [Cust 1 CEM]
OR [Total Tran Amt] / 100 < [Cust 2 CEM]
OR [Total Tran Amt] / 100 < [Cust 3 CEM]
OR [Total Tran Amt] / 100 < [Cust 4 CEM]
OR [Total Tran Amt] / 100 < [Cust 5 CEM] )
OR ([Qual Code] IN("L", "M")
AND [Total Tran Amt] < Qualifier), "Eligible","yes")

May I respectfully suggest you begin omitting spaces from within column
names. You can then write code like this without the square brackets, which
can be helpful. It would look like this:

IIf((QualCode IN("A", "N")
AND (TotalTranAmt / 100 < Cust1CEM
OR TotalTranAmt / 100 < Cust2CEM
OR TotalTranAmt / 100 < Cust3CEM
OR TotalTranAmt / 100 < Cust4CEM
OR TotalTranAmt / 100 < Cust5CEM )
OR (Qual Code IN("L", "M")
AND TotalTranAmt < Qualifier), "Eligible","yes")

In terms of your table design, it might be more normalized to have the
Cust#CEM values in a separate table. This way, you could use an aggregate
MAX function and reduce the above code by having only one line where you now
have 5. In addition, you could then store as many, or a few CEM values as
needed. As you progress in the ability to code queries and need to be able
to write them in text, you'll begin to appreciate this more and more. There
are also some user benefits if it is done this way, if the user must add or
remove CEM values from a list. Exactly how that would be done depends
somewhat on your application and design, so I'll stop there for now.

Tom Ellison

Try this --
Expr2: IIf([Immed Credit]![Qual Code]="A" Or [Immed Credit]![Qual Code] "N"
And ([Immed Credit]![Total Tran Amt]/100<[Immed Credit]![Cust 1 CEM] or
[Immed Credit]![Total Tran Amt]/100< [Immed Credit]![Cust 2 CEM] or [Immed
Credit]![Total Tran Amt]/100< [Immed Credit]![Cust 3 CEM] or [Immed
Credit]![Total Tran Amt]/100< [Immed Credit]![Cust 4 CEM] or [Immed
Credit]![Total Tran Amt]/100< [Immed Credit]![Cust 5 CEM] ), "Eligible","yes")

Im am trying to create the following:
If Qual=A, an dif (Transaction Amount/100) is less than highest CEM then
"Eligible"
If Qual=L, and if Transaction Amout is less Qualifier, then "Eligible"
If Qual=M, and if Transaction Amount is less Qualifier, then "Eligible"
If Qual=N, and if (Transaction Amount/100) is less than highet CEM then
"Eligible"
___________________________________________________________________
This is what I built:
Expr2: IIf([Immed Credit]![Qual Code]="A" Or "N" And [Immed Credit]![Total
Tran Amt]/100<[Immed Credit]![Cust 1 CEM] or [Immed Credit]![Cust 2 CEM] or
[Immed Credit]![Cust 3 CEM] or [Immed Credit]![Cust 4 CEM] or [Immed
Credit]![Cust 5 CEM] then "Eligible","yes")
____________________________________________________________________
message returns:
Expression contains invalid syntax
You may have entered and operand without and operator