Error 3085 Undefined function nz in expression - its not the references

[Alisa]

I have someone using my database as an mde, using the 2003 runtime, on a computer that has XP with Office 2007 (including Access 2007). Eveything works fine, except they get error 3085 on a line of code that is running an Insert SQL statement.

What is weird is that the SQL statement does not include the nz function. It does include a simple UDF for rounding, but that UDF doesn't return an error.

I also have code that checks the references on startup, and none of them are coming up as missing or broken. I have also double checked each reference and they have the exact same version number of every reference (dao360.dll, mscomctl.ocx, etc.). So if it is a "diambiguation" issue, I don't know how to fix it, or even identify it.

The code runs on several other computers with various combinations of XP, Vista, Office 2003, and Office 2007 with no problems.

I am stumped. What else could cause this error? Even if you don't really know, but you have a vague idea, please post it because I can't even think of anything else to investigate at this point.

 

[gemma-the-husky]

can you post the code that is producing the error

 

[Alisa]

I was attempting to simplify enough to post something, but it is substantial procedure to begin with, and on top of that the SQL in question is based on several subqueries and a UDF. By the time I posted all of that I would probably fill up several pages, and who would have time to read it anyway? Can you tell me what you would be looking for that might cause this type of problem? Just to give you an idea of what is going on, the procedure that has the error has a dao recordset, then the SQL string includes a value from the record set. The SQL executes to write a bunch of stuff to a temp table, from which a report is later generated. The error occurs on docmd.runsql.

 

[gemma-the-husky]

just fishing generally, - i would have thought if access is reporting a nz, there must be one somewhere buried in an expression maybe - do you have a SQL back end, (that may not like nz - although your other systems aren't complaining)

can you display the sql to yourself in a msgbox before running it, to see exactly what it looks like

 

[Rabbie]

As it is working correctly on other computers it looks like it is a local issue. Have you tried Compact and Repair?

Also it could be worth checking that the subqueries are working as expected.

Is it a split DB using the same BE as the ones that are working

 

[Alisa]

You are right - I just went back and double checked, and one of the subqueries DOES include the nz function. But shouldn't it still work?

 

[Alisa]

Rabbie, it compacts on exit (they are in the runtime, so they can't do it manually). It is split, but we don't share a BE. I can't have them check the subqueries individually, but they work fine on my computer.

 

[Rabbie]

Yes it should work.

It can be worth testing if NZ works in another place in that DB.

 

[Alisa]

I think it must be working in other places, because I use it everywhere, but I haven't checked anywhere in particular. I will see what that yields.

 

[Alisa]

It appears the nz is NOT working in some other places - there is a field on a form that has an expression like nz(field1,0)+nz(field2,0), etc. that is displaying 0 instead of the correct value. I have never heard of nz not working, but I just found this:
http://www.klippert.com/TCC/Blog/2005/06/access-make-null-zero-its-nothing-when.html

When you want to display zeroes in text boxes (or datasheet columns) when there is no value in a field, the standard method is to surround the value with the Nz() function, to convert a Null value to a zero. However, this doesn't always work, especially in Access 2003, which is much more data type-sensitive than previous versions. In these cases, you can force a zero to appear instead of a blank by using two functions: first Nz() and then the appropriate numeric data type conversion function, such as CLng or CDbl. Here is a sample expression that will yield a zero when appropriate:

NoAvailable: CLng(Nz([QuantityAvailable]))

Have you guys ever heard of this? It is going to be a lot of work if I have to find every nz in the whole program.

 

[Alisa]

ok, more info:
I removed the nz from the subquery, that code runs fine now. BUT, I tried using the method above (CDbl(nz([Field] . . . ) on the form, and that is still showing zeros. At this point, it seems like I can't use nz at all, are there some settings on their computer that could be causing this?

 

[gemma-the-husky]

offhand i think sql doesnt like some access terms - eg vbcrlf - perhaps it just objects to nz in action queries - but im not 100% sure - i think ive had issues with nz in some queries - trouble is you sort them at the time, and forget about it.

 

[Alisa]

But I am not dealing with SQL - both FE and BE are in Access
Also, nz is not working in expressions on forms either.

 

[Rabbie]

But I am not dealing with SQL - both FE and BE are in Access
Also, nz is not working in expressions on forms either.

Alisa, Your query is written in SQL. That's how Access stores queries. Gemma meant there may be issues when you use NZ in an action query.

 

[Alisa]

Oh, right, but nz isn't working on forms either, so that can't be it.

 

[Alisa]

Solved!

I had them install SP3 for Office 2003 and that appears to have fixed the problem. I didn't realize that when you deploy the runtime, it isn't necessarily all the way patched. It is strange, because it works fine on computers WITH Office 2003 WITHOUT SP3, but breaks on computers WITH Office 2007 but WITHOUT the service pack. Anyhow, thank you all for your suggestions.