ryan_uk
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.OL EDB.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!!
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.OL
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!!
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).Va lue)
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).Va
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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,vbL ongDate)
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
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,vbL
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
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
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.
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
For c = 1 To rs.Fields.Count - 1
If VarType() = vbDate Then
List_Audit.List(r, c) = _
Format(rs.Fields(c).Value,
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
Format(rs.Fields(c).Value,
/gustav
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.
I'm well confused now.
Use the constant vbDate and it will work.
/gustav
/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)
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)
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 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
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
List_Audit.List(r, c) = _
Format(rs.Fields(c).Value,
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.
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,
there too.
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!!
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,
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.R ecord_Crea te_Date,"d d-mm-yyyy" ), ...
SELECT CONVERT(VarChar(???), EventDate, 131), ....
See Cast and Convert in help.
For Jet I think you can use FORMAT:
SELECT Format(Source_Test_Event.R
1. Changing the query as per your requirements.
or
2. Formatting rs field in the vb code.