Let's say I have the following Insert Statement - which I do.
Code:
 strWriteData = "Insert Into Master_Review(CR_Number,Review_Date,Reviewer_Name,Service,CTAS_Score,Diagnosis_Name,Procedure_Name, " & _
                "ER_Admit_Datetime,Service_Admit_DateTime,Unit_Admit_Datetime,Hospital_Disch_Datetime,Expected_Length_Of_Stay,Disch_Order_Datetime) " & _
                "VALUES ('" & frmMain.txtCRNumber & _
                          "','" & frmMain.DTPFirstReviewDate.Value & _
                          "','" & frmMain.lblUserName.Caption & _
                          "','" & Format(frmMain.cboDocService.ItemData(frmMain.cboDocService.ListIndex), "00000") & _
                          "','" & frmMain.cboCTAS.Text & _
                          "','" & frmMain.txtDiagnosis.Text & _
                          "','" & frmMain.txtProcedure.Text & _
                          "','" & dtERDate & _
                          "','" & dtServiceAdmitDate & _
                          "','" & dtUnitAdmitDate & _
                          "','" & dtHospDischDate & _
                          "','" & frmMain.txtELOS.Text & _
                          "','" & dtDischOrderDate & "')"
Here is my problem. Of all the dates listed above (e.g. dtUnitAdmitDate, dtHospDischDate) only one (dtERDate) is required. As a result, the user may send in one, several, or all dates. That's not an issue until I go to execute this statement.

If I change the variables to a string and set the values = vbNullstring then Access has a fit and won't accept my Insert. If I change the types to dates (as they are now) then I can never set the dates to null or blank when nothing has been entered for them...there's always some junk in there.

How do I say something like:

If there is a date then
dtERAdmitDate = date
Else
dtERAdmitDate = <some format access will accept that contains no value>
End if


How do I get around this?