IIF Statement

[Benn]

Hi guys, I'm trying to use the following iif statement in a query and it doesn't seem to be working.

VAT: IIf([Charge]="NO","0",([Rate]/100*[Sales]))

"Charge" is a field in one of my tables (also used in the query)

"Rate" is also a field in on the of the tables.

"Sales" however is a calculated field in the query (E.g. "Sales: (calculations)

Maybe this is where I am going wrong? However, even with the 'Charge' field set to "NO" it will display the result "0".

Thanks for any help.

 

[raskew]

Couple of observations:

1) Is VAT a text or number field? Iif([Charge] = "NO", "0" ..... that indicates it's a text field, yet if [Charge]<>"NO", you're performing a numeric calculation: ([Rate]/100*[Sales])

2)

However, even with the 'Charge' field set to "NO" it will display the result "0"

. What's the problem? That's exactly what you coded it to do: Iif([Charge] = "NO", "0"

Bob

 

[Benn]

Hi sorry meant to put that even with the field set to "NO" it still displays "#ERROR"

VAT isn't defined as number or text as I'm defining it in the Field box wihtin the query.

 

[raskew]

Hi again -

Would you please post your query SQL.

Bob

 

[Benn]

I hadn't had a look at the SQL before (Beginner here ) but I think I can see some problems, doesn't look it's looking up the correct tables. Anyway -

=========Begin SQL========

SELECT Customer.CustomerID, Customer.[First Name], Customer.Surname, Customer.[Address Line 1], Customer.[Address Line 2], Customer.Town, Customer.City, Customer.Postcode, Customer![Sales Rate]/100*[Invoice]!Cost AS Discount, [Invoice].Cost, [Invoice].[Paid?], Orders.Bill_no, Orders.Date, Orders.Destination, Orders.PCS, Orders.Weight, Orders.[Rate], Orders.Charge, IIf([Charge]="NO","0",([Rate]/100*[Sales])) AS VAT, [Cost]-[Discount] AS Sales
FROM (Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID) INNER JOIN [Invoice] ON Orders.bill_no = [Invoice].bill_no
WHERE (((Customer.CustomerID)=[Enter Customer ID]) AND (([Invoice].[Paid?])=0));

=========End SQL========

Thanks again for taking the time to help

 

[raskew]

Here's an extract from your query.
Try running it and see if it processes without error.

SELECT
    Customer.CustomerID
  , Customer![Sales Rate]/100*[Invoice]!Cost AS Discount
  , [Invoice].Cost
  , [Invoice].[Paid?]
  , Orders.[Rate]
  , Orders.Charge
  , Orders.Bill_no
  , [Cost]-[Discount] AS Sales
FROM
   (Customer 
INNER JOIN
   Orders 
ON
   Customer.CustomerID = Orders.CustomerID) 
INNER JOIN
   [Invoice] 
ON
   Orders.bill_no = [Invoice].bill_no
WHERE
   (((Customer.CustomerID)=[Enter Customer ID]) 
AND
   (([Invoice].[Paid?])=0));

The point being to ensure that the query, minus your problem Iif() statement, processes without problem.

Bob

 

[Benn]

Yeah it runs just fine like that raskew.

 

[raskew]

OK, Great!

Could you post a couple of lines from the resulting query (including the header row). The
intent is to see what the data looks like.

Bob

 

[Benn]

How do I do that? (Sorry )

 

[Benn]

Okay I guess you just mean the output. I was thinking about output SQL which probably doesn't exsist

CustomerID|Discount|Cost|Paid?|Rate|Charge|bill_no|Sales

 

[raskew]

Think I moved too quick on that request. Without additional
software, I can't tell you how to accomplish that.

You had the idea with the header row. What would be helpful
is to see the actual data for a couple of rows.

Bob

 

[pbaldy]

Ah, a cross post. Since it doesn't look like it's been directly addressed:

What is the data type of Charge? If it's Yes/No, try this:

VAT: IIf([Charge]=0,"0",([Rate]/100*[Sales]))

 

[Benn]

Okay, here's a couple of lines of data - (taken a few out, they're irrelevent)

CustomerID|Discount|Cost|Rate|Charge|Sales

J1|2|10|11|No|8
J1|4|20|15|No|16

So the Discount is calculated by Sales Rate/100*[Cost]

-Where Sales Rate is found on another table

Sales are calculated by doing [Cost]-[Discount]

So now I want to do [Rate]/100*([Cost]-[Discount]) but only when [Charge]=YES

 

[Benn]

pbaldy - it isn't currently yes/no I tried that but couldn't get it to work so I am now just using the lookup function to select YES or NO.

 

[raskew]

OK -

Based on the data you provided, I created a table
and then a subsequent query.

This works, dependent on whether [Charge] = "No"
Note that 0 is numeric, not text "0"

VAT: IIf([Charge]="No",0,([Rate]/100*[Sales]))

Bob

 

[Benn]

Hi bob, I'm getting a return of #Error when I use this IIF statement in the query.

Are you using it in the same query or a new query?

 

[Benn]

Okay guys, I have found the source of the problem. Yes the IIF statement works fine I think I'll blame it on access

What I had done is used the "Lookup wizard" datatype and typed the values I wanted. The values I wanted were "Yes" and "No". What Access did (without telling me grrr) was change the data type to "Yes/No" because they're the values I used.

So instead of "Yes" and "No" I just tested it with "A" and "B" and it works fine now.

Thanks for all the help guys, I appreciate it.

 

[raskew]

Glad it finally worked for you.

Bob