When migrating an access application to sql server, sql changes all your access date/time fields to sql date/time format. Well, with access you can store the data as any type of date format (short time, long date, etc). Sql stores it all in one format (ex: 05/28/1979 12:48:00).
Thats fine, because usually you have control over the data before the end user ever sees it and you can make it look nice with a FormatDateTime or whatever.
Well, I ran into a case today, where it was just better to bind a listbox directly to the database and move on. Since we use a sql backend now, the Pick Up Time was displayed as 01/01/1900 17:00...ugly. The VBA listbox doesn't have any property like TextMatrix that allows for easy editing of its values, so I wrote a little routine that reads the listbox into an array, formats the date/time to vbLongTime and writes it back to the listbox (essentially). Here is the code...
Private Function ListBoxModifyDates(ByRef lstBox As ListBox) As Boolean
On Error GoTo Err_Handler
Dim intX As Integer
Dim intY As Integer
Dim varRow() As Variant
Dim strListBox() As String
Dim strRow As String
Dim intArray As Integer
ReDim strListBox(lstBox.ListCount - 1)
For intY = 0 To lstBox.ListCount - 1
strRow = ""
ReDim varRow(lstBox.ColumnCount - 1)
For intX = 0 To lstBox.ColumnCount - 1
varRow(intX) = lstBox.Column(intX, intY)
Next
For intArray = 0 To UBound(varRow)
If IsDate(varRow(intArray)) Then
varRow(intArray) = FormatDateTime(varRow(intArray), vbLongTime)
End If
strRow = strRow & varRow(intArray) & ";"
Next
Debug.Print strRow
strListBox(intY) = strRow
Next
lstBox.RowSource = ""
lstBox.RowSourceType = "Value List"
For intY = 0 To UBound(strListBox)
lstBox.AddItem (strListBox(intY))
Next
ListBoxModifyDates = True
Exit Function
Err_Handler:
MsgBox "Error!" & vbCrLf & vbCrLf & Err.Description
ListBoxModifyDates = False
End Function