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?!!
11 3908 NeoPa 32,556
Recognized Expert Moderator MVP
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
The data type of "Indexed products" is indeed Number, but the error (data mismatch...) remains. If there another solution to my problem?
NeoPa 32,556
Recognized Expert Moderator MVP
What do you have on the line before the IIf(...) bit?
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.)
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 32,556
Recognized Expert Moderator MVP
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!
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")));
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 14,534
Recognized Expert Moderator MVP
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]);
-
-
Unfortnunately it gives an error. "Syntax error in FROM clause"
PEB 1,418
Recognized Expert Top Contributor
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...
:)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Ernie |
last post by:
Hi,
I spent more than an hour wondering why the following works in my
Python 2.3.4 Linux box:
formula = """
def dummy():
print "This is Ok!"
"""
|
by: celsius |
last post by:
Hi folks,
Al Bowers wrote this program on comp.lang.c
Date: 2001-07-09 13:41:58 PST
#include <stdio.h>
int isleap (unsigned yr);
static unsigned months_to_days (unsigned month);
static long...
|
by: alex |
last post by:
I need a more advanced formula than just an average for calculating
items rating.
I have:
raitng value is on scale 1 to 10.
s - sum of all ratings for an item
n - number of rates (votes)
|
by: RJN |
last post by:
Hi
I have a main report and a sub report. I have a formula field on the
main report and one on the sub report. I want the formula in the
subreport to be evaluated after the formula in the main...
|
by: RJN |
last post by:
Hi
Sorry for posting this message again.
I have a main report and a sub report. I have a formula field on the
main report and one on the sub report. I want the formula in the
subreport to be...
| |
by: rjn |
last post by:
Hi
I have a main report in which I have inserted a sub report. I have a formula field on the main report and one on the sub report. I want the formula in the
subreport to be evaluated after the...
|
by: Mike |
last post by:
Hi,
I have three tables in the following structure (simplified):
Table 1: Containing the customers
-------------------------------------------------
create table Customers
(
int...
|
by: Brian VanPelt |
last post by:
I am not a very experienced VB user, but I was trying to make a form
that a user could input a formula for summation.
For example, I would like the user to input the beginning and end
values of...
|
by: barnzee |
last post by:
Hi all, newbie here, but having a go
I am trying to build a stock watchlist in excel 2007 with a dynamic link to a DDE server (paid for from a broker).There is no add-in or plug-in, I just CTL ALT...
|
by: Barry L. Bond |
last post by:
Greetings!
I just got a new Peet Brothers Ultimeter 2100 Weather Station. This
new one has a way to display the heat index, if you press the "dew point"
key twice.
Being aware of all the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |