MS Access query: Return a value based on the value in another field

IFTHENExample-2012-04-10.accdbI am trying to write a query (see qryGMUtility) that returns the commission that we pay on each product sales record.  The commissions we pay are stored in a table called “UtilityPayout”.  
Commissions are only paid when the orders are accepted by the utility, that is, the UtilityStatus="Accepted by Utility".  Therefore, the value in the field “Payout” should be $0 if the UtilityStatus=”Rejected by Utility”, otherwise, the payout value should be  taken from the UtilityPayout table.
Here’s one of the things that I tried without success: IIf([qrytblSalesToo].[UtilityStatus]="Rejected*",0,[Payout])
Attached is a small extract of the db with the query.
Thanks for your help and any pointers that you can give me to help me learn.
BTW, does anyone know why "TCP/IP" is showing up as a topic under "Select Topics" where I submit this question?  I can't get rid of it.
thutchinsonAsked:
Who is Participating?
 
FlysterConnect With a Mentor Commented:
See qryGMUtility of attached file, Commission Field. If you want the query to return only paid commissions, put ">0" (without quotes) in the criteria section.

Flyster
IFTHENExample-2012-04-10.accdb
0
 
thutchinsonAuthor Commented:
I want all the records returned (accepted and rejected).  I want the value in PAYOUT to be -0- when the order is rejected.  I want the Payout value if the order is accepted.
0
 
thutchinsonAuthor Commented:
OK, I'm able to get the Gross Margin on all sales if I make a couple of changes to the code you suggested. it works now like this:

CommissionPayout: IIf([UtilityStatus]="Rejected by Utility",0,[Payout])
Margin: [qrytblSalesToo].[Payment]-[CommissionPayout]


Thanks for your help, Flyster.  I really appreciate it!
0
 
FlysterCommented:
Thank you. I'm glad I was able to assist!
0
All Courses

From novice to tech pro — start learning today.