Trouble with null value in SQL statement

[DEXWERX]

What I do is break out that expression into a custom DateToString function which returns NULL or your quoted value.

edit:

Private Function DateToString(ByVal DateExpr As Variant) As String
    If IsNull(DateExpr) Then
        DateToString = "NULL"
    Else
        DateToString = Replace("TO_DATE('{0}','MM/DD/YYYY')", "{0}", Format(DateExpr, "mm/dd/yyyy"))
    End If
End Function

strWriteData = "INSERT INTO PROD.CSU_Clinic_Info(ID,Institution,Occurs,Clinic_Date,Location_Num,SID_DOC_NUM," & _
                         "Clinic_Num,Clinic_Code,Doc_Index,Rooms_Used,Time_Of_Day,Cancelled," & _
                         "Cancelled_Reason,Special,Hold,Reassignment,Day_Of_Week,Reinstate,Start_Date,End_Date,User_Name,Special_Booked_Date) " & _
                         "VALUES ('" & mUniqueID & _
                         "','" & mInstitution & _
                         "','" & mOccurs & _
                         "'," & DateToString(mClinic_Date) & _
                         ",'" & mLocation & _
                         "','" & mSID_DOC_NUM & _
                         "','" & mClinicNum & _
                         "','" & mClinic_Code & _
                         "','" & mDoc_Index & _
                         "','" & mRooms & _
                         "','" & mtime & _
                         "','" & mCancelled & _
                         "','" & mCancelledReason & _
                         "','" & mSpecial & _
                         "','" & mHold & _
                         "','" & mReassignment & _
                         "','" & mDayOfWeek & _
                         "','" & mReinstatement & _
                         "'," & DateToString(mStart_Date) & _
                         "," & DateToString(mEnd_Date) & _
                         ",'" & mUserName & _
                         "'," & DateToString(mSpecialBookedDate)

conversion from date to string back to date seems very very wrong...