jana
asked on
How to configure column in vbAccelerator Grid to Date Format in Outlook 2007 VBA
We have a successful vbAccelerator Grid working. We were passing the ReceivedTime as a string.
All has been ok until we needed to sort by date. Since it's in a string, it sort wrong, For example, the string value of '3/3/12 1:30:13 PM' appears before '3/3/12 10:32:38 AM'.
We are reading the following manner: vReceived = itm.ReceivedTime
Since we need the column to be "Date/Time" format, can it be set in the Grid so the sort worked?
All has been ok until we needed to sort by date. Since it's in a string, it sort wrong, For example, the string value of '3/3/12 1:30:13 PM' appears before '3/3/12 10:32:38 AM'.
We are reading the following manner: vReceived = itm.ReceivedTime
Since we need the column to be "Date/Time" format, can it be set in the Grid so the sort worked?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Ramante,
uncomment this line in sub SortByColumn:
'.ClearNongrouped
to allow that sort works well also with other columns.
uncomment this line in sub SortByColumn:
'.ClearNongrouped
to allow that sort works well also with other columns.
Ramante
I have already answered this question in my other posts. Please be careful about duplicating the questions since you are wasting points.
Cheers
Chris
I have already answered this question in my other posts. Please be careful about duplicating the questions since you are wasting points.
Cheers
Chris
ASKER
Hi craisin, true but that question is regarding sorting. That is why I indicated that it pertain to my question of dates/configure.
Nevertheless, will try
Nevertheless, will try
ASKER
hi eemit, don't understand your post ID: 37828815 and ID: 37830105. Are they related to Formatting the column to date?
Hi Ramante,
- I am assuming that your problem from this question has not been already solved.
- Solution for this Question is:
You have to sort Grid after it is initially filled (as stated in my post ID: 37828815)
- remember that cell text is stored as a variant
Set properties of the all you "Date" Columns:
.ColumnFormatString("YourD ateColumnN ame") = "dd/mm/yy hh:mm:ss AM/PM" 'or what you want
.ColumnSortType("YourDateC olumnName" ) = CCLSortDate 'or e.g. CCLSortDateMinuteAccuracy
- Sort Grid (using Grid SortObject) e.g.:
add column to sort object 'SortColumn' (if this column is not already in the sort object),
set SortOrder to CCLOrderAscending or CCLOrderDescending,
set the type of sorting 'SortType' - (best way using .ColumnSortType Property or
in AddColumn e.g.: eSortType:=CCLSortDate)
- I am assuming that your problem from this question has not been already solved.
- Solution for this Question is:
You have to sort Grid after it is initially filled (as stated in my post ID: 37828815)
... Are they related to Formatting the column to date?Yes.
- remember that cell text is stored as a variant
Set properties of the all you "Date" Columns:
.ColumnFormatString("YourD
.ColumnSortType("YourDateC
- Sort Grid (using Grid SortObject) e.g.:
add column to sort object 'SortColumn' (if this column is not already in the sort object),
set SortOrder to CCLOrderAscending or CCLOrderDescending,
set the type of sorting 'SortType' - (best way using .ColumnSortType Property or
in AddColumn e.g.: eSortType:=CCLSortDate)
ASKER
You are correct; had to reread my question to see it. I though it was only column format.
Yes, I had already placed that in my code Ramante.
If you look at the bottom of "FillGrid" you see where the "Date" field is sorted via the
"Column_Click" routine. Another way of coding that would be to just repeat the sorting code, but it is more efficient to re-use code already written.
Have you tried my code yet? You seem to be asking questions all over the place when my posted code has solved all your problems already?
Cheers
Chris
If you look at the bottom of "FillGrid" you see where the "Date" field is sorted via the
"Column_Click" routine. Another way of coding that would be to just repeat the sorting code, but it is more efficient to re-use code already written.
Have you tried my code yet? You seem to be asking questions all over the place when my posted code has solved all your problems already?
Cheers
Chris
Public Sub FillGrid(nType As Integer)
Dim olFolder As Object
Dim folderItems As Object
Dim sorteditems As Object
Dim strFilter As String
Dim itm As Object
Dim x As Integer
Dim str As String
Dim nFound As Long
Dim nItem As Integer
Dim cLblCaption As String
Dim cScheme(2) As String
'only getting mail which is received
Set olFolder = Application.Session.GetDefaultFolder(olFolderInbox)
Set folderItems = olFolder.Items
'First display an empty grid then
'stop screen from updating while data is added to screen
DisplayEmptyGrid False
UserForm1.gridItemsFound.Redraw = False
If Len(Trim(UserForm1.txtSearch.Text)) > 0 Then
If nType <= s_PROJECTS Then
cScheme(s_PROJECTS) = "http://schemas.microsoft.com/mapi/string/{00020329-0000-0000-C000-000000000046}/Project"
'cScheme(s_PROJECTS) = "urn:schemas:httpmail:project"
cScheme(s_SUBJECTS) = "urn:schemas:httpmail:subject"
strFilter = "@SQL=" & Chr(34) & cScheme(nType) & Chr(34) & " like " & "'%" & Trim(UserForm1.txtSearch.Text) & "%'"
Set FilteredItems = olFolder.Items.Restrict(strFilter)
If FilteredItems.Count > 0 Then
UserForm1.gridItemsFound.Clear
For nItem = 1 To FilteredItems.Count
AddData FilteredItems(nItem), nType
Next
UserForm1.gridItemsFound.SortObject.Clear
UserForm1.gridItemsFound.ColumnSortOrder(i_RECEIVED) = CCLOrderDescending
UserForm1.gridItemsFound.SortObject.SortColumn(i_RECEIVED) = 1
'The type must be CClSortString to ensure formatting works correctly
UserForm1.gridItemsFound.SortObject.SortType(i_RECEIVED) = CCLSortString
UserForm1.gridItemsFound.Sort
Else
DisplayEmptyGrid True
End If
HighLightRow 1
If UserForm1.gridItemsFound.Rows > 0 Then
UserForm1.gridItemsFound.SelectedCol = nType
UserForm1.gridItemsFound.RowVisible(1) = True
End If
Else
DisplayEmptyGrid True
End If
'update the indicator of number of records found
nFound = FilteredItems.Count
Else
nFound = 0
DisplayEmptyGrid True
End If
cLblCaption = CStr(nFound)
Select Case nType
Case s_SUBJECTS
UserForm1.Label1.Caption = cLblCaption + " Subject" + IIf(nFound <> 1, "s", "") + " found"
Case s_PROJECTS
UserForm1.Label1.Caption = cLblCaption + " Project" + IIf(nFound <> 1, "s", "") + " found"
End Select
'This is where we now force a sort by clicking on the column head (twice)
'to sort into descending order (remove the loop to sort into ascending order)
For x = 1 To 2
'clicking twice to ensure the latest date is at the top
UserForm1.gridItemsFound_ColumnClick i_RECEIVED
Next
SelectAndFocusInputLine
''Now show the screen updated and refreshed
UserForm1.gridItemsFound.Redraw = True
End Sub
ASKER
You are correct, but since that sort didn't quite worked, I thought we needed to set the "format" for the column; that is why we placed this question.
But reading it, part of the question reads "...until we needed to sort by date". That's why I was confused on why I asked eemit about his entries. So you are correct, it seems i am duplicating question as you state in ID: 37831006.
Please excuse my ignorance in this; didn't meant to do this. What we wanted with this question is to know that by Formatting the column would help the sort.
I would like to continue the sort part in the question placed, which I have still problem.
https://www.experts-exchange.com/questions/27668145/Help-on-sorting-vbAccelaratoe-Grid-in-Outlook-2007-VBA.html
Will proceed close the question
But reading it, part of the question reads "...until we needed to sort by date". That's why I was confused on why I asked eemit about his entries. So you are correct, it seems i am duplicating question as you state in ID: 37831006.
Please excuse my ignorance in this; didn't meant to do this. What we wanted with this question is to know that by Formatting the column would help the sort.
I would like to continue the sort part in the question placed, which I have still problem.
https://www.experts-exchange.com/questions/27668145/Help-on-sorting-vbAccelaratoe-Grid-in-Outlook-2007-VBA.html
Will proceed close the question
ASKER
I am rewarding for format purpose.
Set properties of the Column "received":
.ColumnFormatString("recei
.ColumnSortType("received"
(You can also set it in AddColumn Method)
2)
Add this after Grid is filled:
(replace vbalGrid1 with the name of your vbalGrid)
Call SortByColumn(vbalGrid1.Col
3)
Add this sub to your Form
Private Sub SortByColumn( _
ByVal nCol As Long _
)
On Error GoTo Err_Handler
Dim iSortIndex As Long
Dim sTag As String
'NOTE: replace vbalGrid1 with the name of your vbalGrid
With vbalGrid1.SortObject
'.ClearNongrouped
' See if this column is already in the sort object:
iSortIndex = .IndexOf(nCol)
If (iSortIndex = 0) Then
' If not, we add it:
iSortIndex = .count + 1
.SortColumn(iSortIndex) = nCol
End If
' Determine which sort order to apply:
sTag = vbalGrid1.ColumnTag(nCol)
If (sTag = "") Then
sTag = "DESC"
.SortOrder(iSortIndex) = CCLOrderAscending
Else
sTag = ""
.SortOrder(iSortIndex) = CCLOrderDescending
End If
vbalGrid1.ColumnTag(nCol) = sTag
' Set the type of sorting:
.SortType(iSortIndex) = vbalGrid1.ColumnSortType(n
End With
' Do the sort:
Me.MousePointer = vbHourglass
vbalGrid1.Sort
Me.MousePointer = vbDefault
Exit Sub
Err_Handler:
Debug.Print "ERROR (SortByColumn): " & Err.Description
End Sub
4)
You can call it also from ColumnClick event e.g.:
(Assuming you have set the Grid property HeaderButtons = True)
Private Sub vbalGrid1_ColumnClick(ByVa
Call SortByColumn(lCol)
End Sub