I have the following code:
Code:
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 & _
                         "'," & "To_Date ('" & Month(mClinic_Date) & "/" & Day(mClinic_Date) & "/" & Year(mClinic_Date) & "','MM/DD/YYYY')" & _
                         ",'" & mLocation & _
                         "','" & mSID_DOC_NUM & _
                         "','" & mClinicNum & _
                         "','" & mClinic_Code & _
                         "','" & mDoc_Index & _
                         "','" & mRooms & _
                         "','" & mTime & _
                         "','" & mCancelled & _
                         "','" & mCancelledReason & _
                         "','" & mSpecial & _
                         "','" & mHold & _
                         "','" & mReassignment & _
                         "','" & mDayOfWeek & _
                         "','" & mReinstatement & _
                         "'," & "To_Date ('" & Month(mStart_Date) & "/" & Day(mStart_Date) & "/" & Year(mStart_Date) & "','MM/DD/YYYY')" & _
                         "," & "To_Date ('" & Month(mEnd_Date) & "/" & Day(mEnd_Date) & "/" & Year(mEnd_Date) & "','MM/DD/YYYY')" & _
                         ",'" & mUserName & _
                         "'," & "To_Date ('" & Month(mSpecialBookedDate) & "/" & Day(mSpecialBookedDate) & "/" & Year(mSpecialBookedDate) & "','MM/DD/YYYY')"
The problem is the last line highlighted in bold. If the SpecialBookedDate is null, which it often is, I get a type mismatch error, understandably so since it's receiving a null value.

At present, the only solution I can think of is an If statement above the code that says "If Len(mSpecialBookedDate) = 0 Then" and I have the SQL string without the SpecialBookedDate line included, else, the code you see above. This seems like a lot of extra code when there is a simpler solution though. Any other ideas I'm missing here?