Invalid use of null(94) error

[uscitizen]

i am trying to export some tables from my a2k mdb to csv files. here's some code someone's helped me put together. it seems to work pretty well when there's data in a field, but as you might've guessed that's not what it does when it fails to find data in a field in the underlying table. i get the said error #94 when it hits the field i've blocked out in bold. can someone suggest the magic bullet that'd fix it?

Option Compare Database


Public Function ExportLate_Adverse_Events()
On Error GoTo ErrMe
Dim strSQL As String
Dim cn As ADODB.Connection
Dim rsMyData As ADODB.Recordset
Dim intFileNum As Integer
Dim strCSVPathFile As String


strCSVPathFile = "C:\LTE_AE.csv"

strSQL = "SELECT LATE_ADVERSE_EVENTS.Table_Name, LATE_ADVERSE_EVENTS.Protocol_ID, LATE_ADVERSE_EVENTS.Patient_ID,"
strSQL = strSQL & " LATE_ADVERSE_EVENTS.AE_Type_Code, LATE_ADVERSE_EVENTS.AE_Grade_Code,"
strSQL = strSQL & " IIf([AE_Other_Specify]='COMPLETE AS APPROPRIATE','',[AE_Other_Specify]) AS AE_OTHER,"
strSQL = strSQL & " LATE_ADVERSE_EVENTS.AE_Attribution_Code, LATE_ADVERSE_EVENTS.AE_Start_Date FROM LATE_ADVERSE_EVENTS;"

Set cn = CurrentProject.Connection
Set rsMyData = New ADODB.Recordset
rsMyData.Open strSQL, cn, adOpenStatic, adLockOptimistic

intFileNum = FreeFile(0)

Open strCSVPathFile For Output Access Write As #intFileNum

'*****
Do Until rsMyData.EOF
Write #intFileNum, rsMyData!Table_Name, rsMyData!Protocol_ID, rsMyData!Patient_ID, _
IIf(rsMyData!AE_Type_Code = 0, "", CLng(rsMyData!AE_Type_Code)), IIf(rsMyData!AE_Grade_Code = 0, "", CInt(rsMyData!AE_Grade_Code)), _
rsMyData!AE_OTHER, IIf(IsNull(rsMyData!AE_Attribution_Code), "", CInt(rsMyData!AE_Attribution_Code)), CLng(Format(rsMyData!AE_Start_Date, "yyyymmdd"))

rsMyData.MoveNext
Loop
'*****

Close #intFileNum
ExitMe:

Set rsMyData = Nothing
Set cn = Nothing
Exit Function

ErrMe:
MsgBox Err.description & " (" & Err.Number & ")", vbOKOnly, "Error writing Late Adverse Events file"
Resume ExitMe

End Function


“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[mndrlion]

Instead of
IIf(IsNull(rsMyData!AE_Attribution_Code), "", CInt(rsMyData!AE_Attribution_Code))

use

Nz(rsMyData!AE_Attribution_Code, "")

 

[uscitizen]

hi mndrlion,

tried out your suggestion and happy to say not it works as we want when AE_Attribution_Code's left empty in the underlying table. BUT, it also gives you null when we enter a typical value in this field, e.g. the number 4?

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln