VBA - Formatting the date within a recordset
Posted on 2007-10-17
I have the classic problem of date formats within SQL/VBA. Its driving me nuts but I think it is relatively easy to resolve.
I have my regional settings as UK so the date default is dd/mm/yyyy
Within my access DB I have the format of the date field (called EventDate) as dd/mmm/yyyy hh:mm:ss ampm which is the equivalent of 17/oct/2007 10:06:23 AM (which is when I typed this).
Now the issue is that when I retrieve the data stored within the DB using SQL and VBA the date reverts to the US format of mm/dd/yyyy hh:mm:ss ampm. So using the example above the result from the rs is 10/17/2007 10:06:23 AM.
The results of the rs are returned into a listbox which contains 3 columns not into a textbox which I could force the format of.
The code I use is below:
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim r As Long, c As Long
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & frmAdmin.Text_DB_Location.Value
rs.Open "SELECT EventDate, EventDescription, FreeText FROM Audit WHERE ID = '" & ID.Value & "' ORDER BY EventDate DESC, EventDescription ASC", cn
If rs.EOF = True Then List_Audit.AddItem "No audit history information available"
If Not rs.EOF Then
List_Audit.ColumnCount = rs.Fields.Count
r = 0
Do Until rs.EOF
If rs.Fields.Count > 1 Then
For c = 1 To rs.Fields.Count - 1
List_Audit.List(r, c) = rs.Fields(c).Value
r = r + 1
Ignoring all the over bits what I am trying to do is have the EventDate field within the listbox display exacty as I put it into the DB e.g. dd/mmm/yyyy hh:mm:ss ampm and not displayed as its native format.