Nz function stopped working

  • Thread starter Thread starter maoverby
  • Start date Start date
M

maoverby

Please help!

What would cause the Nz function to stop working in a copy of Access
2003? I have a series of queries that worked previously, but today all
returned a ton of "type conversion failures" and every numeric result
off an Nz() returns the "Error#". I moved the database to a PC running
Access 2002 and it works fine.

Thanks in advance
 
OK, I scanned, and un-registered and re-registered VBA and DAO 3.6 a
few times, and rebooted inbetween, I'm still having the same problem.
I haven't noticed any other functions being wiped out. Is there a
'cheap fix' i can write in vb that can mimic the Nz function?

Thanks!
 
That is an interesting problem ... maybe this will help you out (I threw it
together for this post so its "Air Code" .. no warrenties expressed or
implied :) :

Public Function fNz(varValue As Variant, Optional varValueIfNull As Variant)
As Variant

If IsNull(varValue) Then
If IsMissing(varValueIfNull) Then
Select Case VarType(varValue)
Case vbInteger
fNz = CInt(varValue)
Case vbLong
fNz = CLng(varValue)
Case vbSingle
fNz = CSng(varValue)
Case vbDouble
fNz = CDbl(varValue)
Case vbCurrency
fNz = CCur(varValue)
Case vbDate
fNz = CDate(varValue)
Case vbString
fNz = CStr(varValue)
Case vbBoolean
fNz = CBool(varValue)
Case vbDecimal
fNz = CDec(varValue) 'Note: You can only use Variants
for this type
Case vbByte
fNz = CByte(varValue)
Case vbVariant
fNz = CVar(varValue)
Case Else
fNz = varValue
End Select
Else
fNz = varValueIfNull
End If
Else
fNz = varValue
End If

End Function
 
OK, I scanned, and un-registered and re-registered VBA and DAO 3.6 a
few times, and rebooted inbetween, I'm still having the same problem.
I haven't noticed any other functions being wiped out. Is there a
'cheap fix' i can write in vb that can mimic the Nz function?
Click to expand...

It sure sounds like a reference or library problem of some sort. Did
you find any references marked MISSING?

The Nz() function is defined in the Access Application library itself.
Are you sure your application is referencing the correct version of the
Microsoft Access Object Library? Normally, Access automatically updates
that library reference when you move a database from one PC to another,
but maybe something prevented it in this case.
 
I tried running a query using Brent's fNz code above, and I get the
exact same error as Nz, no runtime error, but the values in the results
are all #ERROR. It seems as if something is having a problem with null
handling. But the IIF statement works fine.

The only libraries I'm attached to are VB for apps, Access 11.0, and
DAO 3.6. I'm using Access 2003. Looks I'm staring at a lot of
rewritten queries, or an re-install.
 
I tried running a query using Brent's fNz code above, and I get the
exact same error as Nz, no runtime error, but the values in the
results are all #ERROR. It seems as if something is having a problem
with null handling. But the IIF statement works fine.

The only libraries I'm attached to are VB for apps, Access 11.0, and
DAO 3.6. I'm using Access 2003. Looks I'm staring at a lot of
rewritten queries, or an re-install.
Click to expand...

Assuming you have some kind of code corruption, try creating a new,
blank database and importing all objects into it. Compile that and see
if it gives the same error.
 
tools, macro, security-- just make sure it's not set to high..

oh shoot; just go ahead and move it to low and see if that fixes it.

-Aaron
 
That did it..it was originally set at medium. I switched it to low,
and Nz() was working again. How Nz() gets tagged as an "unsafe
expression" is beyond me. Thank you very much!
 
If one of your library files has become corrupt then not
using Nz() may only be a partial answer, a band-aid fix.

From your post the problem has appeared without you having
changed anything on your A2K3 which adds another point to
the score for corruption... No other software has been
installed or removed has it (bit of a long shot but always
worth asking)?

I'd go with Dirk's create a new database and import all
objects and see what happens then. Or you could create a
new database, create a test table with some data in and
create a query against that table which you think should
fail.

Interested to see what happens next...

--
Nick Coe (UK)
http://www.alphacos.co.uk/




In
(e-mail address removed) typed:
 
Nick;

I already answered that question.

If you used Groups.google.com you could see THE TRUTH about what goes
on in this forum.

As it is; microsoft blocks my posts BECAUSE I SPEAK THE TRUTH AND I
STAND UP FOR WHAT IS RIGHT

-Aaron
 
Back
Top