• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1122
  • Last Modified:

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!!
0
ryan_uk
Asked:
ryan_uk
  • 4
  • 4
  • 4
  • +3
1 Solution
 
answer_meCommented:
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.
0
 
WikkardCommented:
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)

0
 
Leigh PurvisDatabase DeveloperCommented:
What you're describing is odd - unless you're using an ActiveX listbox.  (Due to the behaviour you're describing and the code you're using to assign values).

(If you are - and this *is* Access you're working in - why are you doing so?  What is it offering that you want particularly?  The native listbox will offer great binding by comparison).

If you're stuck on keeping the ActiveX version - then you'll (as has been suggested above) need to assign a text value (formatted date).
The format which you use for displaying dates (regional settings) doesn't impact their storage at all.  So it's all about presentation. :-)

To get the format you ask for you'd need a subtle variation though.
i.e. instead of just Month you'd use MonthName
FormatDate = day(thedate) & "/" & MonthName(month(thedate),True) & "/" & year(thedate)
of just format the results directly as you assign it to the list column.

For c = 1 To rs.Fields.Count - 1
    If rs.Fields(c).Type = adDate Then
        List_Audit.List(r, c) = Format(rs.Fields(c).Value, "dd/mmm/yyyy hh:nn:ss AMPM")
    Else
        List_Audit.List(r, c) = rs.Fields(c).Value
    End If
Next c
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
WikkardCommented:
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
0
 
ryan_ukAuthor Commented:
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

0
 
Leigh PurvisDatabase DeveloperCommented:
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.
0
 
WikkardCommented:
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.
0
 
Gustav BrockCIOCommented:
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
0
 
Gustav BrockCIOCommented:
Oops, should read:

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

/gustav
0
 
ryan_ukAuthor Commented:
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.  
0
 
Gustav BrockCIOCommented:
Use the constant vbDate and it will work.

/gustav
0
 
Leigh PurvisDatabase DeveloperCommented:
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)
0
 
ryan_ukAuthor Commented:
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
0
 
Gustav BrockCIOCommented:
> 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
0
 
Leigh PurvisDatabase DeveloperCommented:
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.
0
 
ryan_ukAuthor Commented:
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!!
0
 
rheitzmanCommented:
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"), ...
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 4
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now