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.
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.