"OR" IN QUERY

[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

 

[Graeme Richardson]

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.

 

[Lynn Trapp]

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))

 

[Bill Gargan]

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