Using ISNULL()

[Guest]

I've got a left join query that I need a 0 to be included in the a specific
column that would normally be <NULL> in the left join.

I'm trying to write the query using SELECT ..., ISNULL(<COLUMN>, 0), but I
get a "Wrong number of arguments used with function in query expression
'ISNULL(<COLUMN>, 0)'"

Is the ISNULL function defined for access 2003? And if so, what arguments
should I be passing to it. All docs that I've discover say to use the two arg
function.

Thanks for the help in advance,

Andy

 

[Baz]

IsNull is the wrong function to use, it simply takes one argument and
returns true or false depending on whether the argument is null. What you
are looking for is the Nz function:

SELECT ..., Nz(<COLUMN>, 0)

 

[Jamie Collins]

I'm trying to write the query using SELECT ..., ISNULL(<COLUMN>, 0), but I
get a "Wrong number of arguments used with function in query expression
'ISNULL(<COLUMN>, 0)'"

Is the ISNULL function defined for access 2003? And if so, what arguments
should I be passing to it. All docs that I've discover say to use the two arg
function.

Sounds like you are referring to SQL Server's proprietary ISNULL. As
is the nature of proprietary syntax, it is not consistently
implemented between products from the same vendor. Best IMO to stick
to the ANSI standard, where possible, which in this case is COALESCE,
being a shorthand for a CASE statement. Sadly, Access/Jet SQL syntax
does not include COALESCE or even the CASE so you must use an
equivalent proprietary construct e.g.

IIF(my_column IS NULL, 0, my_column) AS my_column

While you can also use Nz(), be aware that it relies on the Access
object model, so any stored queries (VIEW and PROCs) containing Nz()
will error if accessed outside of the Access user interface (e.g. from
within Excel) unless an instance of Access is used.

Jamie.

--

 

[Guest]

In an Access Context, IsNull is a VBA function that determines whether the
value of a variable, control, or field is Null. In Jet SQL, you would not
use the IsNull function; rather, you would use Is Null.

This is not necessary:

IIF(my_column IS NULL, 0, my_column) AS my_column

The correct syntax is Nz(my_column,0)

You are correct regarding using it in Excel and SQL Server; however, I saw
nothing in the OP's post related to either of those products.

 

[Jamie Collins]

In an Access Context, IsNull is a VBA function that determines whether the
value of a variable, control, or field is Null.

Access/Jet SQL DML has an ISNULL *expression*, as distinct from a
function, as distinct from VBA.

This is not necessary:


The correct syntax is Nz(my_column,0)

Whose version of "correct" and "not necessary"? If you are expressing
an opinion, please make this more clear e.g. use 'IMO'.

I tried to make it clear in my original reply: "Nz() will error if
accessed outside of the Access user interface". My IIF version works
everywhere, your Nz() version only works with Access. Mine is verbose,
yours is concise. Mine is 'swings', yours is 'roundabouts'. Yet you
assert yours as the one true syntax. Why is that?

In Jet SQL, you would not
use the IsNull function; rather, you would use Is Null.

But I could use the ISNULL expression. Why else would it be in the
Access/Jet SQL DML?! And again, why are you telling me to use only one
syntax, being your version, when there is more than one way to skin
this cat?

You are correct regarding using it in Excel and SQL Server; however, I saw
nothing in the OP's post related to either of those products.

Excel is merely a popular example of something used to query Access/
Jet data. When I say, "outside of the Access userface" I think it's
nice to give a popular usage example. Why code for one application
when you can easily code for them all? I get the attraction of Nz()
and if Access/Jet SQL had Nz() then I'd use it but I feel you and the
group should be aware of it's drawbacks.

Jamie.

--

 

[Baz]

Access/Jet SQL DML has an ISNULL *expression*, as distinct from a
function, as distinct from VBA.

Really? What does it do? Can you provide an example?

 

[Jamie Collins]

Really? What does it do? Can you provide an example?

It simply takes one argument and returns true or false depending on
whether the argument is null ;-)

Jamie.

--

 

[Baz]

It simply takes one argument and returns true or false depending on
whether the argument is null ;-)

Jamie.

Oh right, that'll be the VBA IsNull function then.

 

[Jamie Collins]

Oh right, that'll be the VBA IsNull function then.

No, it's the Access/Jet SQL DML ISNULL expression. The Access/Jet
egine has it's own expression library, independent of VBA libraries
and the Access object model, comprising ISNULL among others; broadly,
it mirrors the VBA5 scalar functions (as distinct from methods).

You may be open to a charge of 'splitting hairs', however, because I'm
sure the behaviour is the same for IsNull/ISNULL. This is not the
case, though, for all Access/Jet SQL expressions e.g. the Access/Jet
SQL IIF() expression differents from VBA's IIf() function in that the
VBA version always evaluates both true and false clasues whereas the
Access/Jet expression does not. Another example is Access/Jet's broken
CDEC() expression.

Jamie.

--

 

[Baz]

No, it's the Access/Jet SQL DML ISNULL expression. The Access/Jet
egine has it's own expression library, independent of VBA libraries
and the Access object model, comprising ISNULL among others; broadly,
it mirrors the VBA5 scalar functions (as distinct from methods).

You may be open to a charge of 'splitting hairs', however, because I'm
sure the behaviour is the same for IsNull/ISNULL. This is not the
case, though, for all Access/Jet SQL expressions e.g. the Access/Jet
SQL IIF() expression differents from VBA's IIf() function in that the
VBA version always evaluates both true and false clasues whereas the
Access/Jet expression does not. Another example is Access/Jet's broken
CDEC() expression.

Jamie.

It's you who is splitting hairs. Access/Jet SQL supports VBA functions. So
this has a certain (and less-than-perfect) technical implementation. Does
this make those functions part of the SQL DML? If you want to think so then
fine, but as far as I'm concerned they are VBA functions.

If I write my own function in VBA and use it in a query, is that function
then part of the SQL DML? I guess it is by your definition.

 

[Jamie Collins]

Access/Jet SQL supports VBA functions.

Yes, Access enables VBA6 and UDF (plus its own) functions to be used
in Access/Jet SQL but such 'support' is not native to the engine. If
you use such functions in the SQL definition of a stored Query object
then you will cripple the VIEW/PROC when used outside of the Access
environment e.g. someone wanting to pull data into their Excel
workbook.

Does
this make those functions part of the SQL DML?

No because, as I said above, the engine has its own implementation of
expressions which, not coincidentally, have close equivalents in the
VBA5 libraries. Try using a VB6 function (e.g. Replace) outside of
Access e.g. run this from a standard module in an Excel workbook:

Option Explicit

Sub ThisWorks()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
..Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe1.mdb"
With .ActiveConnection
MsgBox .Execute("SELECT ISNULL(0);")(0)
End With
End With
End Sub

Sub AndThisDoesNot()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
..Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe2.mdb"
With .ActiveConnection
MsgBox .Execute("SELECT REPLACE('A', 'A', 'B');")(0)
End With
End With
End Sub

I'm using VBA so why does the second sub procedure fail? The answer is
because the expression library has no REPLACE function. There is no
direct causation between VBA and the non-existent missing REPLACE,
although it seems there once was a policy than Jet SQL expressions
should broadly mirror VBA scalar functions. Shame that MS never got
around to updating the expression library to take account of the
advancements of VBA6 (a REPLACE() expression would be nice), though on
the flip side it's a shame they could break with policy and include
useful things not found in VBA (Nz() would be nice even without a name

If I write my own function in VBA and use it in a query, is that function
then part of the SQL DML? I guess it is by your definition.

No, that's not my position. Consider the following classification with
examples:

Access/Jet SQL e.g. IIF()
VBA6 library e.g. Replace()
Access Object Library e.g. Nz()
Your application e.g. a UDF.

as far as I'm concerned they are VBA functions.

Good for you but in a public forum your classification could be
misleading others.

Jamie.

--

 

[Guest]

Touchy, touchy

--
Dave Hargis, Microsoft Access MVP

Access/Jet SQL DML has an ISNULL *expression*, as distinct from a
function, as distinct from VBA.

Did the OP post in an SQL room with a question, or was this Access?

Whose version of "correct" and "not necessary"? If you are expressing
an opinion, please make this more clear e.g. use 'IMO'.

IIf statements are very slow to execute and should only be used when
necessary. Just because you like them doesn't make them the correct choice.

I tried to make it clear in my original reply: "Nz() will error if
accessed outside of the Access user interface". My IIF version works
everywhere, your Nz() version only works with Access. Mine is verbose,
yours is concise. Mine is 'swings', yours is 'roundabouts'. Yet you
assert yours as the one true syntax. Why is that?

I contend more correct for the case at hand.
Don't understand your reference to swings and roundabouts. Makes no sense,
sorry.

But I could use the ISNULL expression. Why else would it be in the
Access/Jet SQL DML?! And again, why are you telling me to use only one
syntax, being your version, when there is more than one way to skin
this cat?

Can you please point out where the ISNULL expression is in the VBA or Access
Help files? I have not previously heard of this and cannot find it other
than the VBA IsNull function. You can use the IsNull function in Jet SQL,
but it is more standard to use IS Null.

Excel is merely a popular example of something used to query Access/
Jet data. When I say, "outside of the Access userface" I think it's
nice to give a popular usage example. Why code for one application
when you can easily code for them all? I get the attraction of Nz()
and if Access/Jet SQL had Nz() then I'd use it but I feel you and the
group should be aware of it's drawbacks.

Okay, so show me an example using Powerpoint.

 

[Guest]

Jamie, I don't think you know that much about Access. If you do, it looks
like you are stuck somewhere prior to 97.
There is a Replace function in VBA
Check out the Split and Join functions as well.
This is an Access newsgroup, Not an SQL and Excel and what ever else
newsgroup.

 

[Baz]

He's right that the Replace function doesn't work from outside Access just
as, for example, Nz doesn't.

His broader point, though, that the support for (most) VBA functions in
Access/Jet SQL means that they are part of the DML is pedantic, irrelevant,
and a matter of opinion. I'm done here.

 

[Jamie Collins]

His broader point, though, that the support for (most) VBA functions in
Access/Jet SQL means that they are part of the DML

But that's not my point! Rather, my point is that Access/Jet SQL
expressions are not the same entities as the VBA functions of the same
name.

Jamie.

--

 

[Jamie Collins]

Jamie, I don't think you know that much about Access. If you do, it looks
like you are stuck somewhere prior to 97.
There is a Replace function in VBA

Please be courteous enough to read my post before replying to it. I
said, "Try using a VB6 function (e.g. Replace)... VBA6 library e.g.
Replace()." If you read my post, why do you feel the need to inform
me, "There is a Replace function in VBA"?

Check out the Split and Join functions as well.

While this works

a = Split("hello mum", " ")

we are after all in the 'Queries' room and I can't get the Split to
work in a query e.g.

? CurrentProject.Connection.Execute("SELECT Split('hello mum', ' ');")
(0)

generates an error, "Undefined function 'Split' in expression", in the
Access Immediate Window (Access 2007 FYI).If it did work, what data
type would the return value be? One the new fangled multi-valued types
or a 1NF violation?

I know how to use JOIN in a query but such usage differs from the VBA
Join function ;-)

This is an Access newsgroup, Not an SQL and Excel and what ever else
newsgroup.

The OP said, "I'm trying to write the query using SELECT ...,
ISNULL(<COLUMN>, 0)... Is the ISNULL function defined for access
2003?" It sounded to me like the OP's knowledge of an ISNULL
expression with that certain usage is from something external to
Access. SQL Server has one such an ISNULL expression:

http://msdn2.microsoft.com/en-us/library/ms184325.aspx

SQL Server 2005 Books Online
ISNULL (Transact-SQL)

Replaces NULL with the specified replacement value.

ISNULL ( check_expression , replacement_value )

I'm not jumping to any conclusions but the OP having a SQL Server
background seems to fit.

Jamie.

--

 

[Jamie Collins]

Can you please point out where the ISNULL expression is in the VBA or Access
Help files?
I have not previously heard of this and cannot find it other
than the VBA IsNull function. You can use the IsNull function in Jet SQL,
but it is more standard to use IS Null.

Just bear with me while I pull out the specification for the Jet 4.0
engine... no can't find it. I must have the Jet 3.n engine specs here
somewhere... No, I remember now: Microsoft never published one.

Can you please point out where the ISNULL expression is in the VBA or Access
Help files?
I have not previously heard of this and cannot find it other
than the VBA IsNull function. You can use the IsNull function in Jet SQL,
but it is more standard to use IS Null.

Just bear with me while I pull out the specification for the Jet 4.0
engine... no can't find it. I must have the Jet 3.n engine specs here
somewhere... No, I remember now: Microsoft never published one.

I can demonstrate that the Access/Jet IIF expression behaves
differently to the VBA IIF function. It is not documented but it is
demonstrable. Microsoft announced CHECK constraints but didn't give
any kind of specification about how they work, just a few (flawed)
usage examples. And so on and so on. It's not my fault the
documentation is substandard! Perhaps as an Access MVP you could ask
around...?

Okay, so show me an example using Powerpoint.

If all you ever use is Access then it matters not whether the
functionality is provided by Jet-via-Access or by ACE-via-Access or by
VBA-via-Access or by Access-Object-Model-via-Access. In my experience
it is a common task to use Excel to import data from a database (I've
been asked to do it twice today alone and that isn't even my job. I've
never been asked to do the same for Powerpoint).

No big deal, I'm just pointing out that using Replace, Nz, a UDF, etc
will cripple an otherwise useful VIEW. I'm just offering a different
way of looking at things. If consuming the data in Excel (or another
application) is not a factor then disregard my advice. But please
don't try to tell us that your view of the world is the only correct
one.

Jamie.

--

 

[Jamie Collins]

my point is that Access/Jet SQL
expressions are not the same entities as the VBA functions of the same
name.

I've done a bit of digging and I must admit I got things a bit wrong:

http://msdn2.microsoft.com/en-us/library/Bb177901.aspx

SQL Expressions
Access Developer Reference

"The Microsoft Access database engine uses the Microsoft Visual Basic
for Applications (or VBA) expression service to perform simple
arithmetic and function evaluation. All of the operators used in
Microsoft Access database engine SQL expressions (except Between, In,
and Like) are defined by the VBA expression service. In addition, the
VBA expression service offers over 100 VBA functions that you can use
in SQL expressions."

Also see the list of files that comprise Jet:

How to obtain the latest service pack for the Microsoft Jet 4.0
Database Engine
http://support.microsoft.com/kb/239114

Expsrv.dll
Vbajet32.dll

Of the above two, only Expsrv.dll is a COM component; set a reference
to it in a VS.NET project and you get a full Interop.VBA assembly. So
if there is an Access/Jet engine flavour of VBA it is, for all
practical purposes, to be considered as being part of VBA itself. This
is a simplistic view, considering all the components and
interdependencies between them (e.g. defining how IIF() changes in
behaviour when used in SQL code), but I feel it's probably the correct
one.

This reminded me that Brendan Reynolds Access MVP blogged on this
topic a while back:

Conditional Expressions in JET SQL (in five parts)
http://brenreyn.blogspot.com/

In summary, frustrated by lack of documentation Brendan Reynolds
resorts to testing all the functions by trial and error.

Things I'd like to point out in mitigation include the fact that
someone more respected than me in the community has made a similar
mistake of assuming Jet has its expressions independent of VBA and the
same person sees value in distinguishing functionality that is
dependent on the Access environment (I think Brendan Reynolds even
mentions Excel <g>).

One question that remains: if Access/Jet SQL expressions are
considered the same entites as VBA expressions, why can't I use VBA6
expressions such as 'Replace' in Access/Jet SQL outside of the Access
environment?

Jamie.

--