nz function not working on other pc

In the problematic computer, make sure all the code compiles correctly.  In VB Editor, click Debug, select Compile, and make sure all the code compiles cleanly.  If any of the code is highlighted, rem it out, or delete it, and re-compile until there are no problems.  I've had situations where functions would not function because of bad code remnants.

< I have already looked for missing references>
sometimes it is not a missing reference but a left over reference to a dll that was replace by a different version of something or-other (Office upgrades are usually the culprit but not always) try unchecking any reference that you can , CLOSE the ref dialog and then re-open (this forces VBA to read from registry for anything that can be listed that is not selected) and select the ref again. And yes, I have seen this issue screw up the VBA functions eventhough the problem is not with that library.

Steve

If you can't access the references on the other PC (for runtime/mde issues or whatever) at least re-registering your references on the target PC is frequently productive.
In particular DAO is a big culprit for becomming out of synch.  (It doesn't have a gazzilion distinct versions like the ADO dll).
Usually:
Start>Run> RegSvr32 "C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll"

GRayL --- The code compiles correctly.

stevbe --- I have unchecked all of the references that I can.  Closed the ref dialog opened it back up and then checked them again.  No luck the query still returns #error for the column that uses the nz() function.

LPurvis --- I regeistered then DAO reference still no luck.


I went and removed all of the forms, reports and code from the database.   I then removed all of the references except the references Visual Basic for Applications and Microsoft Access 11.0 Object Library.  Compiled the database and tried the query, still no luck.  I still get #error for the column that has the nz() function in it.

What about re-registering all the other references too?

And checking your versions of DAO and Jet - with a working machine's equivalent dll's.

what is the NZ statement you have in the field?

LPurvis

What is the refernce for the Visual Basic for Applications?

I tried to re-register the file that the Microsoft Access 11.0 Object Library refers to.  I recieved the message that the file was loaded but the DLLRegisterServer entry point could not be found.  Then it also said that the msacc.olb does not appear to be a .dll or ocx file.  Which it is not but that is what is refered to in the references dialog box.


stevbe

nz([typedid],0)

Can you show us the query from each pc and indicate which one works?

This is the SQL of the Query.

SELECT CBool(Nz(dbo_tblContractTerm.TypeID,0)) AS Include, qryLine.LineDesc, qryLine.LineID AS LineID, dbo_tblContractTerm.DisplayOrder, dbo_tblContractTerm.TypeID
FROM qryLine INNER JOIN dbo_tblContractTerm ON qryLine.LineID = dbo_tblContractTerm.LineID
WHERE (((dbo_tblContractTerm.TypeID)=[Forms]![frmContractType]![TypeID]));

It is the same database I just copied the database from one pc to the next.

Yes sorry - I didn't mean re-registering those built in libraries.
You'd likely find a re-install of Access what you want there (and may well end up being what you have to do anyway).

I've used

Function fReReg()
On Error GoTo errHere

    Dim ref As Reference
    Dim strRefs As String
    Dim intCount As Integer
    Dim intLoop As Integer
    Dim varPath()
   
    intCount = Application.References.Count
   
    ReDim Preserve varPath(1 To intCount, 1 To 3)
   
    On Error Resume Next
    For Each ref In Application.References
        intLoop = intLoop + 1
        varPath(intLoop, 1) = ref.FullPath
        varPath(intLoop, 2) = ref.Name
        varPath(intLoop, 2) = ref.BuiltIn
    Next
    On Error GoTo errHere
   
    Set ref = Nothing
   
    For intLoop = 1 To intCount
        'If varPath(intLoop, 3) <> True Then
            fRegSvr CStr(varPath(intLoop, 1))
        'End If
    Next

exitHere:
    Exit Function

errHere:
    MsgBox Err.Description
    Resume exitHere
   
End Function

Function fRegSvr(strPath As String) As Long
   
    Const cReg = "cmd /c ""RegSvr32  /s """
    Dim strRegPath As String
   
    strRegPath = cReg & strPath & """"""
    Debug.Print strRegPath
   
    ShellWait (strRegPath)
   
End Function

(Using ShellWait from http://www.mvps.org/access/api/api0004.htm)


Another thing you can do to protect yourself from SQL errors in function implementations is to create your own VBA versions of them.
e.g.
Public Function fNz(varVal, varAlt)

    fNz = Nz(varVal, varAlt)

End Function

And then use fNz([typedid],0) in your query.  (As SQL is more twitchy in the VBA library that it has available).
Just a thought - but ultimately your problems must lie in the installation / status of software or components on one machine compared to the other.

not that I think this has anything to do with it but why force CBool when you can specify the result of NZ ...

SELECT Nz(dbo_tblContractTerm.TypeID, False)

Perhaps to get a boolean value from TypeID which I imagine might be *any* number?

e.g. CBool(22) = True

Not sure for what purpose of course though :-)
An answer to the question "Is there a related Type or not?" perhaps?

Leigh:  What am I missing?

? 22
 22
? cbool(22)
True
? val(cbool(22))
 0

The purpose of the cbool is to be able to show all of those that apply to a contract with a check mark beside them and those that do not apply to not have a check mark.  This is an inherited database.

I think that unless some one else has any other ideas I might just go for the reinstall of Access to see if that fixes it.

Just the mass re-register I mentioned - or the wrapping.
(Or there - install lol)

Ray - yes an odd quirk isn't it.
Technically any non zero value equates to True when tested as a boolean result.
So CBool behaves that way too.  Which returns the VBA values True or False.

But CBool(22) = True
Val(CBool(22)) = 0 because Val(True) = 0
A quirk of the value no doubt.  i.e. it isn't interpreting the value before considering the Val function.
If you force an evaluation of the value first then it all comes good.

Val(Eval(True)) = -1
and so
Val(Eval(CBool(22))) = -1

Leigh, thanks.  Excellent explanation as usual.  I got there by:

? val(cbool(22)*1)
-1
without really knowing why.  Your eval() made it all clear.

Cool, cheers Ray - another fun immediate window session huh? :-)

After I uninstalled and reinstalled Office 2003 applied all of the update from MS still no luck.  The query will still not work on the one pc.

Does it run fine if you remove the Nz?
What if you then use a different function?
(Or a user defined one - as I suggested before).

What references do you actually have in your application?

(Does Date() return a value in a query?)

Yes it does.
Yes it does.
User Defined one - no it does not.
I am trying to avoid going through the whole database and changing every reference to the nz function to something else.


See the earlier post for what references I have in the database.

Date() reurns the current date.

Fair enough (though it would be simple enough to write code to change all Nz to fNz in your queries).
Does Nz work on another field?
Or fail on every field in the query (or other queries).

Then delete the query and start over.  Maybe something in the query generator is 'busted'.  

And were you saying that even the User Defined version failed?
So even in code is Nz failing?

e.g. from the immediate window
?Nz(Null,0)
Does the return 0 - or does it fail?

LPurvis

LPurvis -- The user defined function worked from the immediate window but not from the query.

GRayL -- I deleted the query and started over with no luck.

Sounds like there's some problem with the SQL engine's use of the VBA library.
I'm surprised Date() is still working - that's usually the first culprit - but then Nz is an Access function - not a VBA one.

When you re-installed Access - did you perform a re-install?
Or did you fully uninstall it all - reboot - and then install from scratch?
(I've known the former not do the trick where the latter has).

Also - to check those dll versions for preceise version match (Jet dll in particular perhaps).

I did the control panel uninstall ---- no reboot ------ then install.

And the dlls?

C:\WINDOWS\system32\MSJET35.DLL
and
C:\WINDOWS\system32\msjet40.dll

Comparing versions on your working pc with the annoying one? :-)

I copied both .dll files and registered them still no luck.

What other .dll files should I be checking?

And the database that works on your machine points to the same data that the other one that fails?

Is there anything else that you can think to describe that might be relevant?

(We've not mentioned the inevitable compact/repair and decompile and re-import all into new app?)

Yes both pcs are using the same data source.

I can not think of anything else to help describe this situation.

I createda new app with the one query in it.  In a new database the nz() function still is not working.  I am sure that we have it down to the pc itself being the culprit.

Or the installation of Access on that machine.  

I will give the reinstall another try witha reboot in between the uninstall and the install.

I would like to close this question and split the points between LPurvis and GrayL.  Would anyone object?

Hi.

Did we get you anywhere in the end?
(Even if it was just to a "it's all buggered - I'll wipe it!"  :-)

I am at the "I'll wipe it" stage in the game.

Oh well, I greatly appreciate everyones help with this.

Thanks, sorry I could not be more help.