Iif Formula

[Tischtennisbal]

I'm looking for a formula to convert data.

The table Products contains a column called "Indexed products" in which every product have (or don't have) a percentage.

Using the iif formula, I want to "convert" the data into yes (Y) or no (N).
E.g. Product 1 = 5%, then it should be Yand if no percentage is stated than N.

So far i have this written in the criteria in the query:

IIf([Products]![Indexed product]>0;"Y";"N")

Only the formula doesn't work... It says "Data mismatch in criteria expression"

Who knows the answer?!!

[NeoPa]

Try :-

IIf(IsNull([Products]![Indexed product]), "N", "Y")

And be careful where you assign it.
The result is a string expression so if you assign it to a number or date field you will get

Data mismatch in criteria expression

[Tischtennisbal]

The data type of "Indexed products" is indeed Number, but the error (data mismatch...) remains. If there another solution to my problem?

[NeoPa]

What do you have on the line before the IIf(...) bit?

[Tischtennisbal]

Nothing.

I've put

IIf(IsNull([Indexed product]);"N";"Y")

in the criteria range of "Indexed product" (in the query).

No other info or formulas are added to the criteria. (I hope this is what you mean.)

[Tanis]

Convert the Percentage to a string, Str([YourField]) then perform your if test on that.
ConvertToString : Str([Percentage])

YesOrNo: IIf([ConvertToString]=0,"N","Y")

[NeoPa]

Tischtennisbal,

Open your query for design, convert view to SQL (SQL View from the View menu), and then copy what you see into this thread so I get a clue as to what you're trying to say.

NB If you keep changing the code from what I put in (',' converted to ';') it will NEVER work for you.
Let the clipboard be your friend - COPY & PASTE!

[Tischtennisbal]

SQL CODE:

SELECT Spend.[3Po product code], Products.ProductName, Products.Category, Profit_centre.[Profit centre code], Profit_centre.Region, Spend.[Local Supplier name], Supplier.[3PO Supplier name], Mothercompany.Mothercompanyname, Spend.[3PO Agreement number], Spend.[Delivery date], Spend.[Volume delivered KG], Spend.[Invoiced amount EUR], Spend.[Total spend DDP/EUR], Products.[Indexed product]
FROM (Mothercompany INNER JOIN Supplier ON Mothercompany.Mothercompanycode = Supplier.Mothercompanycode) INNER JOIN (Profit_centre INNER JOIN (Products INNER JOIN Spend ON Products.Code = Spend.[3Po product code]) ON Profit_centre.[Profit centre code] = Spend.PcCode) ON Supplier.[Supplier code] = Spend.[3PO supplier code]
WHERE (((Products.[Indexed product])=IIf(IsNull([Indexed product]),"N","Y")));

[Tischtennisbal]

I hope that is useful to you. I use the output to calculate savings in excel, but the information comes from this query.

The only thing needed is access to convert the percentages to Y or N.

I have no clue, I aint brilliant with Access. My hopes are on you!

Thanks for helping! :-)

[MMcCarthy]

OK What you are trying to do doesn't make any sense:

Try this instead...

 
SELECT Spend.[3Po product code], 
Products.ProductName, 
Products.Category, 
Profit_centre.[Profit centre code], 
Profit_centre.Region, 
Spend.[Local Supplier name], 
Supplier.[3PO Supplier name], 
Mothercompany.Mothercompanyname, 
Spend.[3PO Agreement number], 
Spend.[Delivery date], 
Spend.[Volume delivered KG], 
Spend.[Invoiced amount EUR], 
Spend.[Total spend DDP/EUR], 
IIf(IsNull([Indexed product]),"N","Y") As Indexed
FROM (Mothercompany INNER JOIN Supplier ON Mothercompany.Mothercompanycode = Supplier.Mothercompanycode) INNER JOIN (Profit_centre INNER JOIN (Products INNER JOIN Spend ON Products.Code = Spend.[3Po product code]) ON Profit_centre.[Profit centre code] = Spend.PcCode) ON Supplier.[Supplier code] = Spend.[3PO supplier code]);

[Tischtennisbal]

Unfortnunately it gives an error. "Syntax error in FROM clause"

[PEB]

So pls tell us what are the values of [Products]![Indexed product]

If the values are true or false:

IIf(nz([Products]![Indexed product])>0;-1;0)

or

IIf(nz([Products]![Indexed product])<>0;True;False )

but you don't have but that this is equal to [Products]![Indexed product]

coz there isn't evident logic...

:)