Solved [Access] How to use if in query?

[yunhsuan]

Hello~
I have a table record No, Day, Supplier, Item, UnitPrice, Quantity, Tax and TotalPrice. EX: Supplier_1 Item_1 @12*1-$12 Tax-$0
Then, I use this table make a query. In this query, supplier, Item, UnitPrice, Quantity and Tax are combined to Summary.

Summary: [Supplier] & " " & [Item] & " @" & [UnitPrice] & "*" & [Quantity] & "-$" & Round([UnitPrice]*[Quantity],0) & " Tax-$" & [Tax]

But Tax is 0 sometimes. I hope Tax-$0 is not shown in the result when Tax is 0. EX: Supplier_1 Item_1 @12*1-$12
I try to use this:

IF [Tax]>0 THEN [Supplier] & " " & [Item] & " @" & [UnitPrice] & "*" & [Quantity] & "-$" & Round([UnitPrice]*[Quantity],0) & " Tax-$" & [Tax]  ELSE [Supplier] & " " & [Item] & " @" & [UnitPrice] & "*" & [Quantity] & "-$" & Round([UnitPrice]*[Quantity],0) END IF

But it's error. How can I edit it?

My test file: https://reurl.cc/52880q
Thanks in advance.

 

[theDBguy]

Check out the IIf() function.

 

[plog]

What you want is IIf:

MS Access: iif Function

This MSAccess tutorial explains how to use the Access iif function with syntax and examples. The Microsoft Access iif function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE.

www.techonthenet.com

I would simplify your logic though to just control the Tax result. You've got 2 conditions (If/else) and right now they share a lot of the same things--move those same things outside the IIF and just have the IIF produce that which is different:


IIF X=1--> A + B + C + D + E Else--> A + B + C + D + F

A + B + C + D + (IIf X=1--> E Else--> F)

 

[arnelgp]

Summary: [Supplier] & " " & [Item] & " @" & [UnitPrice] & "*" & [Quantity] & "-$" & Round([UnitPrice]*[Quantity],0) & IIF([Tax]<>0, " Tax-$" & [Tax], "")

 

[yunhsuan]

Thanks! It is resolved successfully.