Geeks With Blogs

News
David Christiansen | davidchristiansen.com

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

Posted on Tuesday, June 29, 2004 4:55 PM | Back to top

Copyright © David Christiansen | Powered by: GeeksWithBlogs.net | Join free