Sending dates from VB to Access 2003

[The_Grudge]

Let's say I have the following Insert Statement - which I do.

 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?

[LaVolpe]

If the date variable is not populated by the user, then its value is probably zero, i.e., dtUnitAdmitDate=0#. Can't you supply the word NULL to your sql statement?

If you do add nulls to your db date fields, you may have to test the fields with IsNull() when you read them back.

[The_Grudge]

I'd rather not send anything if I don't have to but I don't know how I can write my Insert Statement for all the various possibilities.

Sending NULL to Access did do the trick I think. I just have to remove all the single quotes from my Insert SQL because Access would take

NULL

but not

'NULL'

[LaVolpe]

Quite awhile back, I designed something similar. It wasn't a single string, but rather a series of IF statements that build the sql string, something like the following. I didn't try to guess which fields of yours are required and which are not.

Dim sFields As String, sValues As String
' first include all required fields in the sFields string
sFields = "CR_Number, Procedure_Name" ' add others as needed
' then the Values for the required fields
sValues = "'" & frmMain.txtCRNumber & "', '" & frmMain.txtProcedure.Text & "'"

' now handle optional fields and values
sFields = sFields & ", Unit_Admit_Datetime"
If dtUnitAdmitDate<>0# Then
    sValues = sValues & ", #" & dtUnitAdmitDate & "#"
Else 
    sValues = sValues & ", " &  Null
End If
' P.S. Don't remember, if inserting a new record must all fields be included in the field list?
' If not, just don't add it and it should be populated with its default new record values which may be Null

' handle other optional fields/values

When the above is all done, you should be able to modify your main sql statement something like:

 "Insert Into Master_Review(" & sFields & ") VALUES (" & sValues & ")"

The above sample was "air code" therefore, typos may exist

[si_the_geek]

Thread moved to Database Development forum (the "VB6" forum is meant for questions which don't fit in more specific forums)


LaVolpe's idea is on the right track, and by using the # signs is also a clear improvement on your original code (which contained no date values whatsoever), but it is still lacking slightly, and is therefore unreliable (it depends on Regional Settings, etc).

I recommend taking a look at the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of this forum)