Link to home
Start Free TrialLog in
Avatar of ryan_uk
ryan_ukFlag for United Kingdom of Great Britain and Northern Ireland

asked on

VBA - Formatting the date within a recordset

Hi all,

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"

rs.MoveFirst
        If Not rs.EOF Then
        List_Audit.ColumnCount = rs.Fields.Count
    End If

    r = 0
    Do Until rs.EOF
        List_Audit.AddItem rs.Fields(0).Value
        If rs.Fields.Count > 1 Then
            For c = 1 To rs.Fields.Count - 1
                  List_Audit.List(r, c) = rs.Fields(c).Value
            Next c
        End If
        r = r + 1
        rs.MoveNext
    Loop

End Sub

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.

Please help!!
Avatar of Answer_Me
Answer_Me
Flag of India image

Why don't you format the output date accordingly. You can do this by:
1. Changing the query as per your requirements.
or
2. Formatting rs field in the vb code.
For example
List_Audit.List(r, c) = day(rs.Fields(c).Value) & "/" & month(rs.Fields(c).Value) & "/" & year(rs.Fields(c).Value)

of course this would not be the best performing code ...
perhaps a function to format the date in the manner you need.
Function FormatDate(thedate)
FormatDate = day(thedate) & "/" & month(thedate) & "/" & year(thedate)
End Function

then just use code like  

List_Audit.List(r, c) = FormatDate(rs.Fields(c).Value)

ASKER CERTIFIED SOLUTION
Avatar of Leigh Purvis
Leigh Purvis
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I agree it is odd behaviour but dates seem to always be a bit problematic for those not in the US.

Another option is to use the built in formatDateTime function
see http://www.w3schools.com/vbscript/func_formatdatetime.asp

basically it takes the date and a constant to specify the date format.
ie
 formatDateTime(thedate,vbLongDate)

Enum Codes:
vbLongDate 1 'Display a date using the long date format: weekday, month day, year
vbShortDate 2 'Display a date using the short date format: like the default (mm/dd/yy)
vbLongTime 3 'Display a time using the time format: hh:mm:ss PM/AM
vbShortTime 4 'Display a time using the 24-hour format: hh:mm
Avatar of ryan_uk

ASKER

Wikkard,

The issue with your suggestion is that it tries to format all of the fields and not the date field and therefore doesn't show all of the text only data.  LPurvis' suggestion resolves this.

Hi LPurvis,

I'm not using an ActiveX listbox.  I'm actually working in Excel VBA and connecting to an Access DB but the principal is the same.  I'm using the native listbox within VBA.

Using your suggestion works.  It doesn't display the monthname but that isn't an issue.  What it has done is display the dd and mm in the correct (aka UK) date format. ;)

It threw me slightly but I realised you put hh:nn:ss rather than hh:mm:ss so anyone else wanting to use this please take note.

Thanks,

Ryan

Though the data is in "Access" the only thing you're really using then is a Jet database.
I saw the "Excel" tag in the question - but discounted it as this was in an Access zone. :-)
(Access is the front end development environment - with excellent integration with DB's, Jet especially).

>> "It doesn't display the monthname"
Really?  And you did include the three "m"s?  "dd/mmm/yyyy hh:nn:ss AMPM"
The control shouldn't have any choice - as you're not assigning a date to be interpretted.  You're assigning a string value (that looks like a date) to the column.
You'd likely want to bear that in mind if you intend to use the data from the control for any particular purpose as opposed to going back to the tables.

>> "hh:nn:ss rather than hh:mm:ss"
Not required as such when used in context (i.e. with other time formatting - which IMO is a bad thing lol) but is the correct formatting.
My bad, I completely missed the fact the you were looping over the columns in the recordset. I was mainly concentrating on explaining how to format the date correctly.
Just force the format of the date field:

    For c = 1 To rs.Fields.Count - 1
      If VarType() = vbDate Then
        List_Audit.List(r, c) = _
          Format(rs.Fields(c).Value, "dd\/mmm\/yyyy hh\:mm\:ss AM/PM")
      Else
        List_Audit.List(r, c) = rs.Fields(c).Value
      End If
    Next c

Note that "mmm" will return the localised short month name.

/gustav
Oops, should read:

Format(rs.Fields(c).Value, "dd\/mmm\/yyyy hh\:nn\:ss AM/PM")

/gustav
Avatar of ryan_uk

ASKER

Actually the strange thing is that this worked temporarily.  I tested the code on another machine and it didn't work.  So I returned to this machine and tried again and it worked fine.  However when I closed the workbook containing the code on this machine and opened it again the code no longer worked.

I'm well confused now.  
Use the constant vbDate and it will work.

/gustav
The date isn't in the first column is it?
As for formatting (thus far) only occurs on the subsequent entires...

Don't know why that would vary from one instance to another though - given the same code.
(The field type is Date isn't it?  Not just storing Date values in a Text field... Again though - that doesn't explain why it's changed)
Avatar of ryan_uk

ASKER

The date is in the first column.  But I will need to replicate this on another form where the date is in the 3rd column.  Incidentally the code didn't work at all for the other form.

I don't understand either.  I thought I had lost the plot but I had someone check that the date was the right way round so it wasn't me!

The field type is Date/Time but im using a custom format not a pre-defined one but that shouldn't matter.

Gustav,

The constant vbDate doesn't seem to work.  I am using VBA rather than VB so this might be why?

Ryan
> I am using VBA rather than VB so this might be why?

No, but you have to use it this way (I was interrupted by a phone call so I missed to finish the code block):

    For c = 1 To rs.Fields.Count - 1
      If VarType(rs.Fields(c).Value) = vbDate Then
        List_Audit.List(r, c) = _
          Format(rs.Fields(c).Value, "dd\/mmm\/yyyy hh\:mm\:ss AM/PM")
      Else
        List_Audit.List(r, c) = rs.Fields(c).Value
      End If
    Next c

/gustav
Custav is referring to a different method of determining the type of data you're working with (rather than comparing the underlying field type).
I don't see the difference it will make though.  If the underlying field's type is Date - the earlier code would pick it up.

If the first column is a Date then you'll need to perform that same check when adding the row item.
And then have something like
List_Audit.AddItem Format(rs.Fields(0).Value, "dd/mmm/yyyy hh:nn:ss AMPM")
there too.
Avatar of ryan_uk

ASKER

There it is!  LPurvis that worked a treat and it displays the monthname correctly now.

Gustav your method also worked and provides exactly the same result if I use the extra line as LPurvis describes:

List_Audit.AddItem Format(rs.Fields(0).Value, "dd/mmm/yyyy hh:nn:ss AMPM")

Thanks guys this cracked it and I'm sure this time!!
If I need a date in a specific format I use T/SQl's CONVERT:

SELECT CONVERT(VarChar(???), EventDate, 131), ....

See Cast and Convert in help.

For Jet I think you can use FORMAT:

SELECT Format(Source_Test_Event.Record_Create_Date,"dd-mm-yyyy"), ...