Please help with IIf function with "Yes/No"

[canrus01]

I have Yes/No column for Discount option. If Yes is checked, than 3% applies.
I am trying to write IIf statement, so if Yes is checked the query calculates how many days left to make a payment to get a discount based on 10 days period. If No is checked, the query calculates how many days left to make a payment based on 30 days period.

When I run this statement

IIf([3% Discount]="Yes",[Bill Date]-Date()+20,"N/A")AS [Discount Days Remaining]

the query returns
#Error

What am I doing wrong?


Here is the complete code



SELECT [Accounts Payable].PaymentID, [Accounts Payable].CustomerID, [Accounts Payable].[Customer Name],
[Bill Date]-Date()+30 AS [Days Remaining],

IIf([3% Discount]="Yes",[Bill Date]-Date()+20,"N/A")AS [Discount Days Remaining],

[Accounts Payable].[Payment Amount], [Accounts Payable].[Bill Date]
FROM [Accounts Payable];
Thank you.

 

[Jon K]

The values of a Yes/No field are: True, False; Yes, No; -1, 0
So remove the quotes surrounding Yes. The quotes are for text only.

IIf([3% Discount]=Yes, [Bill Date]-Date()+20, "N/A") AS [Discount Days Remaining]


Note:
The "N/A" will change [Discount Days Remaining] to text strings. If you need them to be true numbers, you can change "N/A" to Null.
.

 

[ajetrumpet]

I have Yes/No column for Discount option. If Yes is checked, than 3% applies.
I am trying to write IIf statement, so if Yes is checked the query calculates how many days left to make a payment to get a discount based on 10 days period. If No is checked, the query calculates how many days left to make a payment based on 30 days period.

Do you have a column that gives the discount or non-discount period in numbers?? (i.e. 10 days, 30 days, etc...) If so, you could use "IN" (not for check boxes though)....

SELECT [Accounts Payable].PaymentID, [Accounts Payable].CustomerID, [Accounts Payable].[Customer Name], ([paymentperiod]-DateDiff("d",[Bill Date],Date())) AS DaysRemaining
FROM [Accounts Payable];

 

[canrus01]

Thank you very much.
Yes/No is True or False. I am new to SQL, only a week.

How should I edit this statement, it doesn't work.


IIf([3% Discount]=Yes,[Bill Date]-Date()+20, IIF([3% Discount]=No,[Bill Date]-Date()+30,IIF([Bill Date]-Date()+20<=0,"Discount Expired"))) AS [Discount Days Remaining]


Thank you again.

 

[Jon K]

Since IIFs work from left to right, try this:

IIF([Bill Date]-Date()+20<=0, "Discount Expired", IIf([3% Discount]=Yes, [Bill Date]-Date()+20, [Bill Date]-Date()+30)) AS [Discount Days Remaining]

which returns the number of remaining days (in Text strings because of the "Discount Expired" in the expression) only for those whose discount has not expired i.e. [Bill Date]-Date()+20 >0.
.

 

[canrus01]

Thank you. I changed the statement a little and this fix not competely work.
The reason is not all accounts have 3% discount option, not all IIF have "yes" selected.


IIF([Bill Date]-Date()+24<=0, "Discount Expired", IIf([3%
Discount]=Yes, [Bill Date]-Date()+24, [Bill Date]-Date()+34)) AS [Days Remaining to get DISCOUNT]

It returns all records what are <= as "Discount Expired", but some records what are past due (<=o) don't have the discount and should display "Discount N/A"

The function looks like this

IIf([3% Discount]=Yes, [Bill Date]-Date()+24, IFF([3% Discount]=No, "Discount N/A", IIF([Bill Date]-Date()+24<=0, "Discount Expired" ))) AS [Days Remaining to get DISCOUNT]


I tried to change it arround, same thing.
Thanks

 

[canrus01]

OK, I made it work. Here is the code

SELECT [Accounts Payable].PaymentID, [Accounts Payable].CustomerID, [Accounts Payable].[Customer Name],

IIf([Customer Name]="Petty Cash","N/A",([Bill Date]-Date()+27)) AS [Payment Deadline],

IIf([Customer Name]="Petty Cash"," N/A", IIF([3% Discount]=No, "Discount N/A",IIf([Bill Date]-Date()+7<=0, "Discount Expired",IIf([3% Discount]=Yes, [Bill Date]-Date()+7)))) AS [Days Remaining to get DISCOUNT], [Accounts Payable].[Bill Date],
[Accounts Payable].[Amount to Pay]
FROM [Accounts Payable]

UNION SELECT 'TOTAL', "","","","","",Sum([Accounts Payable].[Amount to Pay])
FROM [Accounts Payable];