I am trying to run an access query which works when I write it
directly in the database. However, it fails when an SQL script
containing it is run from C++.
It doesn't recognize the Iff and Nz functions when run through ODBC.
Query is as follows:
INSERT INTO PROJECT_LINK_DEFN (LD_LINKID,
LD_FROM_PROJECTNAME,LD_TO_PROJECTNAME, LD_FROM_TASKUNIQUEID,
LD_TO_TASKUNIQUEID, LD_LINKTYPE, LD_MODELTYPE ) SELECT
Iff(IsNull(MAX(LD_LINKID)),0,MAX(LD_LINKID)) + 1, '', '', 0, 0, 0, 0
FROM PROJECT_LINK_DEFN
Following error is generated:
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'Iff' in
expression. ***
Can anyone tell me the workaround ?
Thanks
Puneet 2 5412
Sorry, Iif() and Nz() function are only recognised when you use Access
(hence it worked when you wrote it in Access). They are not recognised when
you tried to run the Query from C++ since you are not running it inside
Access. Note that from C++, you are actually using only the JET portion and
hence a JET mdb file, NOT an Access mdb file.
--
HTH
Van T. Dinh
MVP (Access)
"Puneet Murgai" <pm*****@realization.com> wrote in message
news:4b**************************@posting.google.c om... I am trying to run an access query which works when I write it directly in the database. However, it fails when an SQL script containing it is run from C++.
It doesn't recognize the Iff and Nz functions when run through ODBC.
Query is as follows: INSERT INTO PROJECT_LINK_DEFN (LD_LINKID, LD_FROM_PROJECTNAME,LD_TO_PROJECTNAME, LD_FROM_TASKUNIQUEID, LD_TO_TASKUNIQUEID, LD_LINKTYPE, LD_MODELTYPE ) SELECT Iff(IsNull(MAX(LD_LINKID)),0,MAX(LD_LINKID)) + 1, '', '', 0, 0, 0, 0 FROM PROJECT_LINK_DEFN
Following error is generated: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'Iff' in expression. ***
Can anyone tell me the workaround ?
Thanks Puneet
Firstly the function is IIF not IFF (Immediate IF).
Secondly IIF is a VBA function which means that when this query is run in
Access it goes through th expression service in order to evaluate the
function, this doesn't happen when run through DAO.
The Nz function is an Access function so the same applies.
The following seemes to do what you want
INSERT INTO PROJECT_LINK_DEFN ( LD_LINKID, LD_FROM_PROJECTNAME,
LD_TO_PROJECTNAME, LD_FROM_TASKUNIQUEID, LD_TO_TASKUNIQUEID, LD_LINKTYPE,
LD_MODELTYPE )
SELECT "0" & Max([LD_LINKID])+1 AS Expr1, '' AS Expr2, '' AS Expr3, 0 AS
Expr4, 0 AS Expr5, 0 AS Expr6, 0 AS Expr7
FROM PROJECT_LINK_DEFN;
Terry
"Puneet Murgai" <pm*****@realization.com> wrote in message
news:4b**************************@posting.google.c om... I am trying to run an access query which works when I write it directly in the database. However, it fails when an SQL script containing it is run from C++.
It doesn't recognize the Iff and Nz functions when run through ODBC.
Query is as follows: INSERT INTO PROJECT_LINK_DEFN (LD_LINKID, LD_FROM_PROJECTNAME,LD_TO_PROJECTNAME, LD_FROM_TASKUNIQUEID, LD_TO_TASKUNIQUEID, LD_LINKTYPE, LD_MODELTYPE ) SELECT Iff(IsNull(MAX(LD_LINKID)),0,MAX(LD_LINKID)) + 1, '', '', 0, 0, 0, 0 FROM PROJECT_LINK_DEFN
Following error is generated: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'Iff' in expression. ***
Can anyone tell me the workaround ?
Thanks Puneet This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Wolfgang Kaml |
last post by:
Hello All,
I have been working on this for almost a week now and I haven't anything up
my sleeves anymore that I could test in addition or change....
Since I am not sure, if this is a Windows...
|
by: GTi |
last post by:
I have a query like:
SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
"ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
"ContactTable1"."Name2" FROM...
|
by: Mike S |
last post by:
Anyone know how I can access a MS Access database from perl?
Any help appreciated.
|
by: monnomiznogoud |
last post by:
Ok, my problem is the following:
I have very complicated Access 97 databases that link through ODBC to
Sybase databases.
Now in some of the forms controls I had queries that used as "where...
|
by: Greg Strong |
last post by:
Hello All,
The short questions are
1 Do you know how to make DSN connection close in Access to Oracle 10g
Express Edition?
&/or
2 Do you know how to make a DSN-less pass-through query...
|
by: Costas Menico |
last post by:
I have an Access database and a table. One of the columns was a memo
field. If I execute a 'select * from table', all the rows would be
retrieved but the columns after the memo column had null...
|
by: boris_amj |
last post by:
Help,
I have an Access database with link tables to a SQL Server 2000
database.
When I run the following query, it works OK:
SELECT Format(,"mmmm yyyy") AS DateTXT,
Sum(='CANCELLED') AS...
|
by: nolanmadson |
last post by:
I'm creating a front-end for some Teradata user maintenance tables in
MS Access. I've started having problems in occasionally not being able
to insert or update records in these tables. I've been...
|
by: magickarle |
last post by:
Hi, I got a pass-through query (that takes about 15 mins to process)
I would like to integrate variables to it.
IE: something simple:
Select EmplID from empl_Lst
where empl_lst.timestamp between...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |