"OR" IN QUERY

  • Thread starter Thread starter Bill Gargan
  • Start date Start date
B

Bill Gargan

I have a query that works -- and need to change one field
in it.

The one that works is:
PRICE: IIf([INVOICE DETAIL]![ITEM TYPE]="MATERIALS",ROUND
([QUANTITY]*[RATE]*1.265,2),ROUND([QUANTITY]*[RATE],2))

It fails when I change it to read:
PRICE: IIf(or([INVOICE DETAIL]![ITEM TYPE]="MATERIALS",
[INVOICE DETAIL]![ITEM TYPE]="SUBCONTRACT"),ROUND
([QUANTITY]*[RATE]*1.265,2),ROUND([QUANTITY]*[RATE],2))

The error message I get when I try to save the query is:
"The expression you entered contains invalid syntax. You
may have entered a comma without a preceding value or
identifier."

Any ideas why this fails.

Thanks,
Bill Gargan
 
Hi Bill, the OR(Expr1, Expr2, ...) syntax is for Excel. It won't work in MS
Access
MS Access requires Expr1 OR Expr2 OR ...

Hence your statement becomes:

PRICE: IIf(([INVOICE DETAIL]![ITEM TYPE]="MATERIALS" OR
[INVOICE DETAIL]![ITEM TYPE]="SUBCONTRACT"),ROUND
([QUANTITY]*[RATE]*1.265,2),ROUND([QUANTITY]*[RATE],2))

Hope this helps, Graeme.
 
You can't use an OR statement like that in an IIF Statement. That is Excel
syntax, not Access. Instead, try this:

PRICE: IIf([INVOICE DETAIL]![ITEM TYPE]="MATERIALS" OR
[INVOICE DETAIL]![ITEM TYPE]="SUBCONTRACT",ROUND
([QUANTITY]*[RATE]*1.265,2),ROUND([QUANTITY]*[RATE],2))
 
Hi,

I got this reply from two folks. Thanks to both of you.

Despite lots of effort and research all I could find in Access 2000 help and
in Access 2000 books was the Excel form.

Thanks again,
Bill Gargan
 
Back
Top