Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Listview: Sorting by date (Word 2002; VBA)

    I am trying to sort a Listview control by a date column. However, the control only seems to sort alphabetically.

    Any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listview: Sorting by date (Word 2002; VBA)

    List items and its SubItems are strings.
    You could add a hidden column (subitem) that contains the numeric value of the dates (use CLng to convert them), formatted as "00000". You can order by this column.

  3. #3
    Lounger
    Join Date
    Jan 2005
    Location
    Orange, California, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listview: Sorting by date (Word 2002; VBA)

    Hans, how would this look...

    itm.SubItems(2) = format(clng(sInputDate), "00000") . .maybe?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listview: Sorting by date (Word 2002; VBA)

    Something like that. Have you tried it?

  5. #5
    Lounger
    Join Date
    Jan 2005
    Location
    Orange, California, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listview: Sorting by date (Word 2002; VBA)

    I wanted to see if I was barking up the right thread. I just gave it a shot and it didn't work. When you convert it to a long data type from a date, is it turning it into a Julian date...is that the idea? I hard coded into my code that handles the column clicks to use the column that date conversion went into.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listview: Sorting by date (Word 2002; VBA)

    Dates are actually stored as numbers, to be precise the number of days since 12/31/1899. For example, 10/17/2005 is stored internally as 38642.

  7. #7
    Lounger
    Join Date
    Jan 2005
    Location
    Orange, California, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listview: Sorting by date (Word 2002; VBA)

    When the date has a time value in it, does it distinguish it as part of that number?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listview: Sorting by date (Word 2002; VBA)

    The time is stored as the decimal part of the number, for example 6:00 AM = 0.25, 12:00 PM = 0.50 and 6:00 PM = 0.75. If you format a date/time value as "00000", the time part will be lost, so if you need to take the time into account, you could use "00000.00000" as format.

  9. #9
    Lounger
    Join Date
    Jan 2005
    Location
    Orange, California, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listview: Sorting by date (Word 2002; VBA)

    Hans, it still isn't working. When I look at the value it just gives a decimal adn 5 zeros (and the date is displaying the time with it). This is what I'm doing:

    itm.SubItems(4) = Format(CLng(dLastLogin), "00000.00000")

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listview: Sorting by date (Word 2002; VBA)

    Of course, by applying CLng, you're stripping away the decimal part, i.e. the time. Why do you use CLng?

  11. #11
    Lounger
    Join Date
    Jan 2005
    Location
    Orange, California, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listview: Sorting by date (Word 2002; VBA)

    Okay, made the change and had another problem. I started another message to you with examples of what I was seeing, but then said, "what would Hans do in a situation like this." Anyhow, gave me an excuse to crack open the code I stole to sort the columns to begin with and correct the code - now it all works fine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •