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

yunhsuan

Member
Local time
Tomorrow, 00:44
Joined
Sep 10, 2021
Messages
54
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
Today, 09:44
Joined
Oct 29, 2018
Messages
21,358
Check out the IIf() function.
 

plog

Banishment Pending
Local time
Today, 11:44
Joined
May 11, 2011
Messages
11,611
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
Tomorrow, 00:44
Joined
May 7, 2009
Messages
19,169
Code:
Summary: [Supplier] & " " & [Item] & " @" & [UnitPrice] & "*" & [Quantity] & "-$" & Round([UnitPrice]*[Quantity],0) & IIF([Tax]<>0, " Tax-$" & [Tax], "")
 

Users who are viewing this thread

Top Bottom