Solved [Access] How to use if in query? (1 Viewer)

yunhsuan

Member
Local time
Today, 14:13
Joined
Sep 10, 2021
Messages
52
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.
Code:
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:
Code:
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

I’m here to help
Staff member
Local time
Yesterday, 23:13
Joined
Oct 29, 2018
Messages
18,792
Check out the IIf() function.
 

plog

Banishment Pending
Local time
Today, 01:13
Joined
May 11, 2011
Messages
10,866
What you want is IIf:


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

..forever waiting... waiting for jellybean!
Local time
Today, 14:13
Joined
May 7, 2009
Messages
16,125
Code:
Summary: [Supplier] & " " & [Item] & " @" & [UnitPrice] & "*" & [Quantity] & "-$" & Round([UnitPrice]*[Quantity],0) & IIF([Tax]<>0, " Tax-$" & [Tax], "")
 

Users who are viewing this thread

Top Bottom