Solved

How to configure column in vbAccelerator Grid to Date Format in Outlook 2007 VBA

Posted on 2012-04-09
11
786 Views
Last Modified: 2012-04-12
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?
0
Comment
Question by:rayluvs
  • 5
  • 4
  • 2
11 Comments
 
LVL 15

Accepted Solution

by:
eemit earned 500 total points
ID: 37827786
Hi Remante,
Set ColumnFormatString Property to "dd/mm/yy hh:mm:ss AM/PM" e.g.:
YourGridName.ColumnFormatString("received") = "dd/mm/yy hh:mm:ss AM/PM"
(or set it in AddColumn Method)

ColumnFormatString - Gets/sets a format string used to format all of the text in the column
(remember that cell text is stored as a variant). Format strings are the same as those used in
the VB Format$ function.
0
 
LVL 15

Expert Comment

by:eemit
ID: 37828815
1)
Set properties of the Column "received":
.ColumnFormatString("received") = "dd/mm/yy hh:mm:ss AM/PM"
.ColumnSortType("received") = CCLSortDate
(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.ColumnIndex("received"))

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(nCol)
 
  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(ByVal lCol As Long)
  Call SortByColumn(lCol)
End Sub
0
 
LVL 15

Expert Comment

by:eemit
ID: 37830105
Hi Ramante,
uncomment this line in sub SortByColumn:
      '.ClearNongrouped
to allow that sort works well also with other columns.
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37831006
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
0
 

Author Comment

by:rayluvs
ID: 37832634
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
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:rayluvs
ID: 37832684
hi eemit, don't understand your post ID: 37828815 and ID: 37830105.  Are they related to Formatting the column to date?
0
 
LVL 15

Expert Comment

by:eemit
ID: 37833972
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)
... 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("YourDateColumnName") = "dd/mm/yy hh:mm:ss AM/PM" 'or what you want
.ColumnSortType("YourDateColumnName") = 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)
0
 

Author Comment

by:rayluvs
ID: 37834084
You are correct; had to reread my question to see it.  I though it was only column format.
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 37835268
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

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

Open in new window

0
 

Author Comment

by:rayluvs
ID: 37837880
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.

http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_27668145.html

Will proceed close the question
0
 

Author Closing Comment

by:rayluvs
ID: 37837955
I am rewarding for format purpose.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Resolve Outlook connectivity issues after moving mailbox to new Exchange 2016 server
Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now