How can I sort dates in a listview (access 2000)

[Guest]

I have started using the litview active x control vs. the typical listbox. I
have a sort function that works fine, but since all data is stored as text it
sorts dates incorrectly. Is there a work around for this??

 

[Duane Hookom]

Maybe go back to a standard list box with a SQL Row Source statement that
sorts on CDate([YourTextDate]).

 

[Guest]

I would do that, but there are 5 different columns that could be used to
sort. 3 of them are date/time fields, the other 2 are text. I would have t
write 5 individual SQL staement to cover each possible sort scenario.

Maybe go back to a standard list box with a SQL Row Source statement that
sorts on CDate([YourTextDate]).

--
Duane Hookom
MS Access MVP

I have started using the litview active x control vs. the typical listbox.
I
have a sort function that works fine, but since all data is stored as text
it
sorts dates incorrectly. Is there a work around for this??

 

[Guest]

Also, the dates are stored as dates in the tables, but any data populated in
a listview is stored in the listview as text.

Maybe go back to a standard list box with a SQL Row Source statement that
sorts on CDate([YourTextDate]).

--
Duane Hookom
MS Access MVP

I have started using the litview active x control vs. the typical listbox.
I
have a sort function that works fine, but since all data is stored as text
it
sorts dates incorrectly. Is there a work around for this??

 

[Duane Hookom]

You can always use code to update/change the Row Source of a list box.

--
Duane Hookom
MS Access MVP

Also, the dates are stored as dates in the tables, but any data populated
in
a listview is stored in the listview as text.

Maybe go back to a standard list box with a SQL Row Source statement that
sorts on CDate([YourTextDate]).

--
Duane Hookom
MS Access MVP

I have started using the litview active x control vs. the typical
listbox.
I
have a sort function that works fine, but since all data is stored as
text
it
sorts dates incorrectly. Is there a work around for this??

 

[Guest]

I figured out the solution.

Private Sub lstreps_ColumnClick(ByVal ColumnHeader As Object)

Dim oListItem As ListItem
Dim i As Integer

i = ColumnHeader.Index - 1

'date columns

If ColumnHeader.Index = 4 Or ColumnHeader.Index = 5 Or
ColumnHeader.Index = 6 Or ColumnHeader.Index = 7 _
Or ColumnHeader.Index = 10 Then
For Each oListItem In lstReps.ListItems
oListItem.SubItems(i) = _
Format$(oListItem.ListSubItems(i).Tag, _
"yyyymmddHHMMSS")
Next oListItem
End If

With lstReps
.SortKey = ColumnHeader.Index - 1
.Sorted = True
If .sortOrder = lvwAscending Then
.sortOrder = lvwDescending
Else
.sortOrder = lvwAscending
End If
End With


'date columns

If ColumnHeader.Index = 4 Or ColumnHeader.Index = 5 Or
ColumnHeader.Index = 6 Or ColumnHeader.Index = 7 _
Or ColumnHeader.Index = 10 Then
For Each oListItem In lstReps.ListItems
oListItem.SubItems(i) = _
CDate(oListItem.ListSubItems(i).Tag)
Next oListItem
End If
End Sub

You can always use code to update/change the Row Source of a list box.

--
Duane Hookom
MS Access MVP

Also, the dates are stored as dates in the tables, but any data populated
in
a listview is stored in the listview as text.

Maybe go back to a standard list box with a SQL Row Source statement that
sorts on CDate([YourTextDate]).

--
Duane Hookom
MS Access MVP


I have started using the litview active x control vs. the typical
listbox.
I
have a sort function that works fine, but since all data is stored as
text
it
sorts dates incorrectly. Is there a work around for this??

 

[Kevin K. Sullivan]

Chuck,

One small change to your formating:

I figured out the solution.

oListItem.SubItems(i) = _
Format$(oListItem.ListSubItems(i).Tag, _
"yyyymmddHHMMSS")

Use "yyyymmddHHNNSS" instead. M is month. N is minute. Always a
confusing one.

HTH,

Kevin