My First MS Access Question: #Error

This started happening with my Access 2000 database in Access 2002, which I just received on my company computer. When I have null field values as a result of a right join, and this field value is to be fed to a function, all I see in the result set for the function on affected records is #Error. The function with the null input parameter is never even called – the function properly deals with nulls, or at least did until I got the upgraded Access 2002. Now the function doesn’t get called at all but rather Access insists I have an error?

Anyone else see this, and find a workaround? I know that Google groups is my friend, but my searching talents seem to be not good enough to find an answer today (so much for a comment I made in another thread recently).

Also, I’ve not tried upgrading the DB to 2002 – but we’re such a mixed environment that I really, really don’t want to do that.

I just tested it – it’s not JUST tables that are joined. Any time I have a null value to pass to a function in a query, I get #Error instead of passing the value to the function. Any ideas?

In my version (Access 2003), a null value passed to a function will produce #ERROR, unless the argument is a variant. So, how is the argument data-typed in your function?

Not typed at all, which is supposed to default to variant. But even if I do type, such as this:

Public Function hiNull(theValue As Variant) As String
If IsNull(theValue) Then
hiNull = “It’s Null”
hiNull = “It’s not null”
End If
End Function

…I still get the #Error indication rather than the having the function called.

Before anyone asks, I know the function isn’t being called, because if I set a breakpoint it doesn’t stop.

This really buggers up a LOT of my queries, and no, I can’t just not show the null records. I could go through and use IIF to test for nulls in the SQL itself, but it shouldn’t be necessary and it’s a lot of extra work. Grrrr…

My previous test used a function identical to yours! As I said, it was in Access 2003, but the function was used in a single table query - the table had nulls for some of the values.

So I went to my other computer (Access 2002), entered your function verbatim, and used it in an outer join which produced nulls. Net result: no problem.

Here is the output

Key  FieldText   FieldLong   Expr1   Expr2   FieldText2   FieldLong2   Expr3   Expr4
1	1x	1	It's not null	It's not null	11x	11	It's not null	It's not null
2			It's Null	It's Null			It's Null	It's Null
3	3x	3	It's not null	It's not null			It's Null	It's Null

So what’s going on? Could be a “Bill Gates Moment”. Have you tried Debug - Compile Everything In Sight?

Couple of hopefully useful suggestions:

I used to find that sometimes, Access just got a stick up its ass over a particular qquery, even though it looked fine in both SQl design views; copying the SQL to the clipboard, deleting the query, then pasting the SQL into a brand new one (and saving as the same name) fixed the phantom problem.

Dunno if your version of Access supports it, but the nz() function should convert a null to a zero wherever necessary; passing your query nz(<value>) or nz([fieldname]) wherever there’s a possibility of it being null might help.

Null is a funny beast; (null=0) is false, (null<>0) is also false - it isn’t zero and it isn’t not zero - it’s null.

Well, I didn’t want to do it, but I got it fixed. I bit the bullet and installed just Access 2000 from the server. Still did the same damn thing – but I know it’s not good to have two MS-Offices on a Windows machine at the same time, so I uninstalled BOTH of them, restarted, and installed 2002 fresh off the server. Seems to have fixed it. And I had previously tried “detect and repair.” More time than I wanted to spend on a Friday at work, but oh well – fixed!

Thanks for all your help!