SQL - Access conversion problem

[DMoney]

I have built a querry in Access in which one field is using the NZ function.
The querry works fine. The problem is when I try and access the querry from
VB in an SQL statement. i get an error that essentially says NZ function not
declared. i am using an ADODB 4.0 reference for the connection. All other
querries not containing the NZ function, pull into VB with no problems. Any
help would be appreciated.

TIA

 

[Ron Weiner]

Nz() is an Access function. Your VB Jet connection to the database can only
use Jet functions. You will need to remove Nz() from the query and use some
custom code on the VB side to replace it. Since most of the time when one
is using VB there are no bound controls, it is not much a problem to move
your Null handler to the code that populates your controls.

 

[Douglas J Steele]

Actually, I believe you can use the IIf and IsNull functions to replace Nz.

Rather than Nz([MyField], 0), try IIf(IsNull([MyField], 0, [MyField])

 

[david epsom dot com dot au]

Or "Is Null":

IIf([myfield] is null,0,[myfield])

Is "is null" ANSI SQL? I know that the way to do this
with servers that didn't support functions like IIF was
sql like this:

select myfield from mytable where myfield is not null
union all
select 0 as myfield from mytable where myfield is null;

(david)

Actually, I believe you can use the IIf and IsNull functions to replace
Nz.

Rather than Nz([MyField], 0), try IIf(IsNull([MyField], 0, [MyField])

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Nz() is an Access function. Your VB Jet connection to the database can only
use Jet functions. You will need to remove Nz() from the query and use some
custom code on the VB side to replace it. Since most of the time when
one
is using VB there are no bound controls, it is not much a problem to move
your Null handler to the code that populates your controls.

 

[DMoney]

Thank you all for your suggestions. To reduce the probability of running
into this in the future, I rewrote the Access querry using IIF is null in
place of the nz function.