Discussion on:

Avoid Null value conflicts between Access and SQL Server

Great article on NULL values in Access vs. Project. This will help alot. One thing I have been unable to find is a good book with detailed information MS Access SQL. There are obviously plenty of Books on MS Access, but I haven't been able to find one that get into SQL specifics for Access that go into any great detail with multiple examples, including functions. Do you know of any books that go into great detail on Access specific SQL? I do a lot of development for ms access and most of my findings are just by trial and error.

The Nz function doesn't work if you use VB6.0 with ADO. Nz only works within the Ms Access environment. Try this speudo code and you wil get a runtime error (Undefined function 'Nz' in expression):

Dim CON As ADODB.Connection
Dim RST As ADODB.Recordset

Set CON = New ADODB.Connection
CON.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Password=;Data Source= .mdb;"
CON.Open

Set RST = New ADODB.Recordset
Set RST.ActiveConnection = CON
RST.Source = "SELECT , Nz( , 0) AS NullField FROM ;"
RST.Open '// Runtime error on open

While Not RST.EOF
Debug.Print RST.Fields(0).Value, RST.Fields(1).Value
Wend

RST.Close
CON.Close
Set RST = Nothing
Set CON = Nothing

The nz() function is not part of the standard visual basic libraries. It is a method of the Access.Application object.

There are two workarounds for this. First, you could add Access.Application to your project. (Hey, I DO have a sense of humour. LOL)

Second, you can write your own nz() function. I generally write my own nz(). It's not a perfect replication, you may need to change the code. But, here's what I use:

Public Function nz(vTarget As Variant, Optional vValIfNull As Variant = 0) As Variant
On Error Resume Next
If IsNull(vTarget) Or IsEmpty(vTarget) Then
nz = vValIfNull
Else
nz = vTarget
End If
End Function

The Access.Application.nz() method will generally return an empty string instead of a zero, when it gets a string type value in the first parameter. You may want to adjust for this.