Access 2000 need an alternative to NZ function

I am using the NZ function in the fiels location.
Notes: NZ([purchaseorderdetail].[Notes],"")

I am having trouble importing this query into a 3rd party program. This program doesn't like this NZ function. When I take out the NZ function it imports perfectly. When I put the NZ function back into my query, the program doesn't want to connect. It says " Value cannot be null. Parameter name: adodb."

I need another way to do my NZ function without using it in the field location, so my 3rd party program will connect to my query.
Please let me know if you need more info or clarity.
Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
mancoiAuthor Commented:
I posted a screen shot of the details section of my query. 

Arthur_WoodConnect With a Mentor Commented:
try this in place of the NZ function:

Notes: [purchaseorderdetail].[Notes] & ""

this will append a "" (zero-length string) onto the values from the table, which will *not* affect notes that have real text, but will convert any NULL values to a zero-length string, which is precisely what your use of the NZ function is doing.

The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Rey Obrero (Capricorn1)Commented:
try this

Notes: NZ([purchaseorderdetail].[Notes],Null)

Is the [Notes] field allow zero length property set to yes
Jim P.Connect With a Mentor Commented:
These are foreign tables in Paradox?  That you are connecting using ADO?

You'll have to dig around in the Paradox guide for that function. In Transact SQL (MS SQL version of SQL) the function is IsNull(MyField,'').
Oracle uses NVL(MyField,'').
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
The below code is the VB equivalent of Nz, as VB does not have Nz.  Perhaps you can re-write it to be Paradox-friendly...
Public Function Nz(vValue as Variant, Optional vValueIfNull as Variant) as Variant

If Not IsNull(vValue)
   Nz = vValue
elseif IsNull(vValue) and (Not IsNull(vValueIfNull) then
   Nz = vValueIfNull
elseif IsNull(vValue) and IsNull(vValueIfNull)
  'User passed null values in both vValue and vValueIfNull.  Put what you want here, like...
   Nz = ""
end if

End Function
mancoiAuthor Commented:

Check this site out.....

How can I change my function to an IIF function? I am using ......Notes: NZ([purchaseorderdetail].[Notes],"")
mancoiAuthor Commented:
Got it using an IIF I will award points....THANKS
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
glad to be of assistance.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.