by Patrick JJ Kavanagh (2 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Tue 24th December 2002
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Keep NULL dates when updating SQL from VB instead of having them defaulting to 1/1/1900. Also, format dates so that they can be written to SQL
VBcoders
' field that is NULL and should stay NULL then you need a workaround.
' What I am doing here is using a variant variable which can be set = NULL
' Then before passing the date to the SQL update/insert check if the date
' variable is empty. If it is, use the variant variable in its place,
' otherwise pass the date variable but formatted so that SQL can handle it
' without giving you errors
' for this example, lets say you have a datetime field in your SQL table
' called DateOfBirth and you have a form in VB with a date-format field
' called txtDateOfBirth. Now, if the vb form field is left empty then you
' want the SQL field to be left as NULL. Also, if the vb field is filled in
' and you may be using dmy, mdy or another format for your vb field but you
' need to ensure that the correct format is passed to SQL then the soultion
' is as shown below.
' declare a variant variable and set to NULL
Dim strVarDate As Variant
strVarDate = Null
Dim strDOB as String
strDOB = txtDateOfBirth.text
'now check your form variable (set to strDOB so we can manipulate)
If Len(RTrim(strDOB)) > 0 Then
' ie a date has been entered, so for SQL change format to one that
' SQL will recognise regardless of your locale settings
strDOB = Format(strDOB, "yyyy-mm-dd")
End If
' now, you pass to SQL either strDOB (if date entered) or
' strVarDate (if no date entered)
' I am passing the field as a command object parameter - you may be doing
' something different like setting up a SQL string. You will see that the
' IIF() statement checks if a date has been entered and if not uses the
' strVarDate NULL variable and if a date was entered then uses the formatted
' strDOB variable.
' this way SQL keeps Null dates Null and handles date entry regardless
' of locale settings.
objCmd.Parameters.Append objCmd.CreateParameter("DateOfBirth", adVarChar, adParamInput, 22, _
IIf(Len(RTrim(txtDateOfBirth.text)) = 0, strVarDate, strDOB))
No comments have been posted about Keep NULL dates when updating SQL from VB instead of having them defaulting to 1/1/1900. Also, form. Why not be the first to post a comment about Keep NULL dates when updating SQL from VB instead of having them defaulting to 1/1/1900. Also, form.
