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.
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”
…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…
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!