Error 94: Invalid use of Null

[tranchemontaigne]

CAVEAT: I have some very poor quality data to work with.

GOAL: Run a series of data quality checks and edits against every field before a "cleaned" record can be INSERTed into a "cleaned" data table.

METHOD:
(1) Assign a variable for every data field in "cleaned" data table
(2) Build a recordset of raw data
(3) Evaluate each field against a set of coded edits
(4) Based upon a test for "null" values, build a dynamic INSERT statement
NOTE: this dynamic statement will build the INSERT INTO clause and the VALUES clause based upon the actual number of fields with values
(5) Loop through recordset to the next record until EOF has been reached

PROBLEM CODE:
(Code fails at row #2 in this snippet when rst1![t13_No_Students_Absent].Value = null)

If IsNull(rst1![t13_No_Students_Absent].Value) = True Then
varNo_Students_Absent = Null
ElseIf Trim(rst1![t13_No_Students_Absent].Value) = "" Then
varNo_Students_Absent = Null
Else
varNo_Students_Absent = CLng(Mid(rst1![t13_No_Students_Absent].Value, 19, 30))
End If

QUESTION: Variant datatypes are designed to store null values. What should I do to set a variant variable equal to a recordset element that stores a null value?

Any assistance would be appreciated.
________
XR125L

 

[Steve R.]

You have complicated variable names and I am not about to try to decipher them. But I guess, I did.

Originally, I wrote that you would probably have to use the NZ function. I hope that I deconstructed your code correctly, see below. I simplified the structure to make it simpler to understand assuming that I figured out what you were attempting. You will have to re-add your recordset stuff back in.

----------------------------------------------------------------------------

No_Students_Absent = RST_No_Students_Absent
If  Trim(No_Students_Absent) = "" Then 
             Students_Absent = Null     
     Else
             Students_Absent = CLNG(Mid(No_Students_Absent, 19, 30))
End If

 

[tranchemontaigne]

Steve R.,

Thanks for the suggestions. While waqiting for a response it occurred to me that the problem was entirely related to nulls. by eliminating nulls I was able to make the problem go away.

Things are working now. Here's the working code, though I do have to admit that it is a bit more complicated than my original posting would suggest.

'set value of vartNo_Students_Absent
If IsNull(rst1![t13_No_Students_Absent].Value) = True Then
vartNo_Students_Absent = "CST-Null"

ElseIf Trim(rst1![t13_No_Students_Absent].Value) = "" Then
vartNo_Students_Absent = "CST-Null"

ElseIf Len(Trim(rst1![t13_No_Students_Absent].Value)) = 18 Then
vartNo_Students_Absent = "CST-Null"

Else
vartNo_Students_Absent = (Mid(rst1![t13_No_Students_Absent].Value, 19, 30))


'test value stored in variable
If IsNumeric(varNo_Nurse_Visits_ILI) Then
vartNo_Students_Absent = CLng(vartNo_Students_Absent )

Else
'test to see if string can be converted to a number
'fnConvertTextToDigits is a custom function (not in MS Access libraries) returns an array containing text and numeric equivalents
vartNo_Students_Absent = fnConvertTextToDigits(Trim(vartNo_Students_Absent ))

'if string cannot be converted to a number, save value to "missing"
If vartNo_Students_Absent (1) = Null Then
vartNo_Students_Absent _Missing = vartNo_Students_Absent (0)
vartNo_Students_Absent = "CST-Null"

Else
vartNo_Students_Absent = vartNo_Students_Absent (1)

End If

End If

End If

________
Marijuana trichomes