|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
|
|
After doing a virus and spyware/adware check, the first
thing to check is the references on the 2003 machine.
This page on Allen Browne's web site explains what to do -
http://www.allenbrowne.com/ser-38.html
--
Nick Coe (UK)
http://www.alphacos.co.uk/
In
news:1159169974.449537.135800[ at ]e3g2000cwe.googlegroups.com,
maoverby[ at ]gmail.com typed:
[Quoted Text] > 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!
Nick Coe (UK) wrote:
[Quoted Text] > After doing a virus and spyware/adware check, the first > thing to check is the references on the 2003 machine. > > This page on Allen Browne's web site explains what to do - > http://www.allenbrowne.com/ser-38.html> > -- > Nick Coe (UK) > http://www.alphacos.co.uk/> > > > > In > news:1159169974.449537.135800[ at ]e3g2000cwe.googlegroups.com, > maoverby[ at ]gmail.com typed: > > 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
|
|
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
--
Regards,
Brent Spaulding
datAdrenaline
"maoverby[ at ]gmail.com" wrote:
[Quoted Text] > > 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! > > Nick Coe (UK) wrote: > > After doing a virus and spyware/adware check, the first > > thing to check is the references on the 2003 machine. > > > > This page on Allen Browne's web site explains what to do - > > http://www.allenbrowne.com/ser-38.html> > > > -- > > Nick Coe (UK) > > http://www.alphacos.co.uk/> > > > > > > > > > In > > news:1159169974.449537.135800[ at ]e3g2000cwe.googlegroups.com, > > maoverby[ at ]gmail.com typed: > > > 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 > > |
|
The equivalent of Nz([MyField], 0) is IIf(IsNull([MyField]), 0, [MyField])
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
<maoverby[ at ]gmail.com> wrote in message
news:1159178763.571228.164300[ at ]h48g2000cwc.googlegroups.com...
[Quoted Text] > > 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! > > Nick Coe (UK) wrote: >> After doing a virus and spyware/adware check, the first >> thing to check is the references on the 2003 machine. >> >> This page on Allen Browne's web site explains what to do - >> http://www.allenbrowne.com/ser-38.html>> >> -- >> Nick Coe (UK) >> http://www.alphacos.co.uk/>> >> >> >> >> In >> news:1159169974.449537.135800[ at ]e3g2000cwe.googlegroups.com, >> maoverby[ at ]gmail.com typed: >> > 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 >
|
|
<maoverby[ at ]gmail.com> wrote in message
news:1159178763.571228.164300[ at ]h48g2000cwc.googlegroups.com
[Quoted Text] > 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?
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.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
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.
Dirk Goldgar wrote:
[Quoted Text] > <maoverby[ at ]gmail.com> wrote in message
> news:1159178763.571228.164300[ at ]h48g2000cwc.googlegroups.com
> > 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?
>
> 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.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
|
|
<maoverby[ at ]gmail.com> wrote in message
news:1159233194.236226.311590[ at ]d34g2000cwd.googlegroups.com
[Quoted Text] > 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.
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.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
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
news:1159178763.571228.164300[ at ]h48g2000cwc.googlegroups.com,
maoverby[ at ]gmail.com typed:
[Quoted Text] > 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! > > Nick Coe (UK) wrote: >> After doing a virus and spyware/adware check, the first >> thing to check is the references on the 2003 machine. >> >> This page on Allen Browne's web site explains what to >> do - >> http://www.allenbrowne.com/ser-38.html>> >> -- >> Nick Coe (UK) >> http://www.alphacos.co.uk/>> >> >> >> >> In >> news:1159169974.449537.135800[ at ]e3g2000cwe.googlegroups.com, >> maoverby[ at ]gmail.com typed: >>> 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
|
|
|