• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Keep NULL dates when updating SQL from VB instead of having them defaulting to 1/1/1900. Also, form

Keep NULL dates when updating SQL from VB instead of having them defaulting to 1/1/1900. Also, form

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

Rate Keep NULL dates when updating SQL from VB instead of having them defaulting to 1/1/1900. Also, form

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

' 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))