Discussion:
isnull() function equivelence in OLEDB jet 4.0
(too old to reply)
Frank Jiang
2004-01-08 01:32:07 UTC
Permalink
I am stuck at this isnull function.

I am using OLEDB jet 4.0 access Access tables. I execute
SQL statement in VB.net.

I am trying to do this in code which I can do in T-SQL.

select isnull(total, 0) from sometable

It doesn't work. In OLEDB jet 4.0 isnull(exp) return 0/-1
which is totally different function. I read that OLEDB
jet 4.0 take SQL-92 standard. So I tried coalesce(exp,
exp, exp..) which is SQL-92 statement and it didn't work
either.

Is there any reference I can check what functions are
available for OLEDB jet 4.0?

Frustrating.

Frank
oj
2004-01-08 02:51:15 UTC
Permalink
Isnull() is a valid Access function. However, it's not the same as T-sql
Isnull().

/*Access*/
select IIF(Isnull(mycol),'NULL',mycol) as MyCol
from TB;

/*Sqlserver*/
select Isnull(mycol,'NULL') as MyCol
from TB;
--
-oj
http://www.rac4sql.net
Post by Frank Jiang
I am stuck at this isnull function.
I am using OLEDB jet 4.0 access Access tables. I execute
SQL statement in VB.net.
I am trying to do this in code which I can do in T-SQL.
select isnull(total, 0) from sometable
It doesn't work. In OLEDB jet 4.0 isnull(exp) return 0/-1
which is totally different function. I read that OLEDB
jet 4.0 take SQL-92 standard. So I tried coalesce(exp,
exp, exp..) which is SQL-92 statement and it didn't work
either.
Is there any reference I can check what functions are
available for OLEDB jet 4.0?
Frustrating.
Frank
Frank
2004-01-08 18:45:52 UTC
Permalink
I got "iff is not defined function" exception.

I am running the SQL from VB.net code and I made the
connection by
myConnection As String
= "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"User ID=Admin;" & _
"Data Source=myDB.mdb"

I checked Jet 4.0 help in Access. But I didn't find
information of isnull() or iff() except for some simple
scalar function like RIGHT, UCASE, ABS.

I guess the worst case is that I have to loop through all
the resultset(datatable) and take care of null value
myself.

:( Frank
-----Original Message-----
Isnull() is a valid Access function. However, it's not
the same as T-sql
Isnull().
/*Access*/
select IIF(Isnull(mycol),'NULL',mycol) as MyCol
from TB;
/*Sqlserver*/
select Isnull(mycol,'NULL') as MyCol
from TB;
--
-oj
http://www.rac4sql.net
Post by Frank Jiang
I am stuck at this isnull function.
I am using OLEDB jet 4.0 access Access tables. I
execute
Post by Frank Jiang
SQL statement in VB.net.
I am trying to do this in code which I can do in T-SQL.
select isnull(total, 0) from sometable
It doesn't work. In OLEDB jet 4.0 isnull(exp) return
0/-1
Post by Frank Jiang
which is totally different function. I read that OLEDB
jet 4.0 take SQL-92 standard. So I tried coalesce(exp,
exp, exp..) which is SQL-92 statement and it didn't
work
Post by Frank Jiang
either.
Is there any reference I can check what functions are
available for OLEDB jet 4.0?
Frustrating.
Frank
.
oj
2004-01-08 18:57:23 UTC
Permalink
Frank,

It's IIF() - Intermediate IF, not IFF. You have a mispell.
--
-oj
http://www.rac4sql.net
Post by Frank
I got "iff is not defined function" exception.
I am running the SQL from VB.net code and I made the
connection by
myConnection As String
= "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"User ID=Admin;" & _
"Data Source=myDB.mdb"
I checked Jet 4.0 help in Access. But I didn't find
information of isnull() or iff() except for some simple
scalar function like RIGHT, UCASE, ABS.
I guess the worst case is that I have to loop through all
the resultset(datatable) and take care of null value
myself.
:( Frank
-----Original Message-----
Isnull() is a valid Access function. However, it's not
the same as T-sql
Isnull().
/*Access*/
select IIF(Isnull(mycol),'NULL',mycol) as MyCol
from TB;
/*Sqlserver*/
select Isnull(mycol,'NULL') as MyCol
from TB;
--
-oj
http://www.rac4sql.net
Post by Frank Jiang
I am stuck at this isnull function.
I am using OLEDB jet 4.0 access Access tables. I
execute
Post by Frank Jiang
SQL statement in VB.net.
I am trying to do this in code which I can do in T-SQL.
select isnull(total, 0) from sometable
It doesn't work. In OLEDB jet 4.0 isnull(exp) return
0/-1
Post by Frank Jiang
which is totally different function. I read that OLEDB
jet 4.0 take SQL-92 standard. So I tried coalesce(exp,
exp, exp..) which is SQL-92 statement and it didn't
work
Post by Frank Jiang
either.
Is there any reference I can check what functions are
available for OLEDB jet 4.0?
Frustrating.
Frank
.
Steve Kass
2004-01-08 18:59:08 UTC
Permalink
It's Immediate IF - not Intermediate IF. You have a mistake. ;)

SK
Post by Greg Obleshchuk
Frank,
It's IIF() - Intermediate IF, not IFF. You have a mispell.
oj
2004-01-08 19:39:57 UTC
Permalink
heheheheh...i'm just human. damn! :~)
--
-oj
Post by Steve Kass
It's Immediate IF - not Intermediate IF. You have a mistake. ;)
SK
Post by Greg Obleshchuk
Frank,
It's IIF() - Intermediate IF, not IFF. You have a mispell.
Frank
2004-01-08 19:06:34 UTC
Permalink
You are right, and I did find this function in Access
help. It's nested deeply in example of expressions\null
value. You cannot even search it out by the "stupid
Answer wizard" and not in Jet 4.0 referrence.

Thanks a lot.
-----Original Message-----
Frank,
It's IIF() - Intermediate IF, not IFF. You have a
mispell.
--
-oj
http://www.rac4sql.net
Post by Frank
I got "iff is not defined function" exception.
I am running the SQL from VB.net code and I made the
connection by
myConnection As String
= "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"User ID=Admin;" & _
"Data Source=myDB.mdb"
I checked Jet 4.0 help in Access. But I didn't find
information of isnull() or iff() except for some simple
scalar function like RIGHT, UCASE, ABS.
I guess the worst case is that I have to loop through
all
Post by Frank
the resultset(datatable) and take care of null value
myself.
:( Frank
-----Original Message-----
Isnull() is a valid Access function. However, it's not
the same as T-sql
Isnull().
/*Access*/
select IIF(Isnull(mycol),'NULL',mycol) as MyCol
from TB;
/*Sqlserver*/
select Isnull(mycol,'NULL') as MyCol
from TB;
--
-oj
http://www.rac4sql.net
in
Post by Frank
Post by Frank Jiang
I am stuck at this isnull function.
I am using OLEDB jet 4.0 access Access tables. I
execute
Post by Frank Jiang
SQL statement in VB.net.
I am trying to do this in code which I can do in T-
SQL.
Post by Frank
Post by Frank Jiang
select isnull(total, 0) from sometable
It doesn't work. In OLEDB jet 4.0 isnull(exp) return
0/-1
Post by Frank Jiang
which is totally different function. I read that
OLEDB
Post by Frank
Post by Frank Jiang
jet 4.0 take SQL-92 standard. So I tried coalesce
(exp,
Post by Frank
Post by Frank Jiang
exp, exp..) which is SQL-92 statement and it didn't
work
Post by Frank Jiang
either.
Is there any reference I can check what functions
are
Post by Frank
Post by Frank Jiang
available for OLEDB jet 4.0?
Frustrating.
Frank
.
.
Greg Obleshchuk
2004-01-08 02:50:45 UTC
Permalink
Frank,
you need to get the JET SQL Help file. Look in your Office directory for
*.chm

you could try

iif(NULL,'This is NULL', 'THisis not NULL')

I can't remember if IIF works through ADO
--
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.aspx. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.aspx. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.aspx. Free icon extraction
program
http://www.ag-software.com. Free programming tools
Post by Frank Jiang
I am stuck at this isnull function.
I am using OLEDB jet 4.0 access Access tables. I execute
SQL statement in VB.net.
I am trying to do this in code which I can do in T-SQL.
select isnull(total, 0) from sometable
It doesn't work. In OLEDB jet 4.0 isnull(exp) return 0/-1
which is totally different function. I read that OLEDB
jet 4.0 take SQL-92 standard. So I tried coalesce(exp,
exp, exp..) which is SQL-92 statement and it didn't work
either.
Is there any reference I can check what functions are
available for OLEDB jet 4.0?
Frustrating.
Frank
Loading...