# My First MS Access Question: #Error **URL:** \ **Category:** Factual Questions\ **Created:** [January 21, 2005, 5:33pm UTC](https://boards.straightdope.com/t/my-first-ms-access-question-error/285939 "2005-01-21T17:33:54Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Balthisar](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/balthisar/32/2825_2.png) [@Balthisar](https://boards.straightdope.com/u/Balthisar)\ **Post date:** [January 21, 2005, 5:33pm UTC](https://boards.straightdope.com/t/my-first-ms-access-question-error/285939/1 "2005-01-21T17:33:54Z")
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. ---
**Author:** ![Balthisar](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/balthisar/32/2825_2.png) [@Balthisar](https://boards.straightdope.com/u/Balthisar)\ **Post date:** [January 21, 2005, 5:55pm UTC](https://boards.straightdope.com/t/my-first-ms-access-question-error/285939/2 "2005-01-21T17:55:05Z")
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? ---
**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\ **Post date:** [January 21, 2005, 5:57pm UTC](https://boards.straightdope.com/t/my-first-ms-access-question-error/285939/3 "2005-01-21T17:57:52Z")
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? ---
**Author:** ![Balthisar](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/balthisar/32/2825_2.png) [@Balthisar](https://boards.straightdope.com/u/Balthisar)\ **Post date:** [January 21, 2005, 7:28pm UTC](https://boards.straightdope.com/t/my-first-ms-access-question-error/285939/4 "2005-01-21T19:28:59Z")
> [@K364](#): > > 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” Else 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… ---
**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\ **Post date:** [January 21, 2005, 9:13pm UTC](https://boards.straightdope.com/t/my-first-ms-access-question-error/285939/5 "2005-01-21T21:13:41Z")
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 ```auto 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? ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [January 21, 2005, 9:33pm UTC](https://boards.straightdope.com/t/my-first-ms-access-question-error/285939/6 "2005-01-21T21:33:30Z")
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(\) 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. ---
**Author:** ![Balthisar](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/balthisar/32/2825_2.png) [@Balthisar](https://boards.straightdope.com/u/Balthisar)\ **Post date:** [January 21, 2005, 10:29pm UTC](https://boards.straightdope.com/t/my-first-ms-access-question-error/285939/7 "2005-01-21T22:29:40Z")
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!