Access Vba - To find null and blank values

Question

I am trying to find the blank values and null values in a table. I am using Asc to assign the values of the table to a variable and then based on their ASCII values differentiating null and blank. But I am getting "runtime error 94: Invalid use of null" when the code tries to read the ASCII value of a null field.

Answer 1

I think you should be using IsNull() to decide if a value is null.

http://articles.techrepublic.com.com/5100-10878%5F11-5034252.html

Answer 2

You can try the following user-defined function to test the table value:

Public Function text_test(str_in as Variant) As Long
'   given input str_in, return -2 if input is Null,
'     -1 if input is zero-length string; otherwise return 0
' use function Nz to test if input is Null and return -2,
'   otherwise check non-null value with Len
'   and return -1 if it is a 0-length string,
'   otherwise return 0 for anything else
    text_test = IIf(Nz([str_in], "null") = "null", -2, _
        IIf(Len(str_in) = 0, -1, 0))
End Function

In the immediate window run a test with different inputs: ?text_test("fred");text_test("");text_test(Null);text_test(9);text_test(False)

Should return: 0 -1 -2 0 0

Note that you cannot use str_in as string in the function declaration since this will cause the same error you refer to in your question.

Answer 3

When I have to deal with return values that can be either Null or zero-length string, I use a function that converts ZLS to Null:

  Public Function varZLStoNull(varInput As Variant) As Variant
    If Len(varInput) = 0 Then
       varZLStoNull = Null
    Else
       varZLStoNull = varInput
    End If
  End Function

This takes advantage of the fact that the VBA Len() function treats a Null and a ZLS exactly the same so that you don't have to handle each case individually.

However, remember that if you use this in a WHERE clause, you'll be losing performance because it can't use the indexes. Thus, in a WHERE clause, you'd test for IS NULL or =""

  SELECT MyField
  FROM MyTable
  WHERE MyField Is Null Or MyField = ""

That will be much more efficient. The varZLSToNull function is most useful when you're appending processed data to a field that has ZLS Allowed set to NO (as it should).

Another thing you should consider is changing your field so that it disallows ZLS, then running a query (using the WHERE clause above without the Is Null) to replace all the ZLS's with Nulls.

Of course, that assumes that your data is not distinguishing between Null and ZLS as meaning two different things (Null meaning "we haven't recorded any value here" and ZLS meaning "we have recorded an empty value here").

Answer 4

encapsulate your code inside a if statement and compare the string value to vbNullString like this:

If (Not (<string> = vbNullString) Then

if the string is NOT null execute your original code

if it is null add an Else block to execute what you need to do if the value is null