Text field sort question

  • Thread starter Thread starter Brucels
  • Start date Start date
B

Brucels

Windows Explorer (Windows XP) sorts a group of files as follows:
1995.203.22.1.tif
1995.203.22.2.tif
1995.203.22.3.tif
1995.203.22.4.tif
1995.203.22.5.tif
1995.203.22.6.tif
1995.203.22.7.tif
1995.203.22.8.tif
1995.203.22.9.tif
1995.203.22.10.tif
1995.203.22.11.tif
1995.203.22.12.tif


The same group, entered in an Access 2002 table (with the field formatted as
a text field), sorts as follows:
1995.203.22.1.tif
1995.203.22.10.tif
1995.203.22.11.tif
1995.203.22.12.tif
1995.203.22.2.tif
1995.203.22.3.tif
1995.203.22.4.tif
1995.203.22.5.tif
1995.203.22.6.tif
1995.203.22.7.tif
1995.203.22.8.tif
1995.203.22.9.tif

Is there any way of changing the Access sort to match the Explorer sort
without having to manually insert zeroes?

Thanks,
Bruce
 
Windows Explorer (Windows XP) sorts a group of files as follows:
1995.203.22.1.tif
1995.203.22.2.tif
1995.203.22.3.tif
1995.203.22.4.tif
1995.203.22.5.tif
1995.203.22.6.tif
1995.203.22.7.tif
1995.203.22.8.tif
1995.203.22.9.tif
1995.203.22.10.tif
1995.203.22.11.tif
1995.203.22.12.tif


The same group, entered in an Access 2002 table (with the field formatted as
a text field), sorts as follows:
1995.203.22.1.tif
1995.203.22.10.tif
1995.203.22.11.tif
1995.203.22.12.tif
1995.203.22.2.tif
1995.203.22.3.tif
1995.203.22.4.tif
1995.203.22.5.tif
1995.203.22.6.tif
1995.203.22.7.tif
1995.203.22.8.tif
1995.203.22.9.tif

Is there any way of changing the Access sort to match the Explorer sort
without having to manually insert zeroes?

Thanks,
Bruce
Click to expand...

In a table, No.
However no one should be looking at the table anyway.
Create a query with all of the fields you have in the table.
Add a new column to the grid.
SortThis:Val(Replace([FieldName],".",""))
Sort the query on this SortThis field.

Now use this query as the record source for your Form or Report.
Remember, however, that the sort order of a query is irrelevant to the
Sort order of a Report, so make sure you use the SortThis field in the
Report's Sorting and Grouping dialog.
 
I don't think Fred's solution will work as desired. With the limited
example set it should work, but with a sample that includes

1995.203.21.1.tif
1995.203.21.12.tif
1995.203.23.1.tif

You will probably see results that are not satisfactory. I would guess
that to get the sorting you want you are going to need a custom function
to create a sort value or you will need to insert the zeroes.

This one is specific to your situation, but it can be generalized.

Public Function fSortSpecial(strIn, _
Optional strDelimit As String = ".", _
Optional LnumSize As Long = 3) As String

'strDelimit = character(s) used to split string
'lNumsize = minimum number of characters to use for number string
'This function is designed to handle only integer number strings
Dim vSplit As Variant
Dim strReturn As String
Dim i As Long
Dim sFormat as string

sFormat = String(LnumSize,"0")
If Len(strIn & "") = 0 Then
fSortSpecial = ""
Else
vSplit = Split(strIn, strDelimit)
For i = LBound(vSplit) To UBound(vSplit)
If IsNumeric(vSplit(i)) Then
strReturn = strReturn & _
Format(Val(vSplit(i)),sFormat) & strDelimit
Else
strReturn = strReturn & vSplit(i) & strDelimit
End If
Next i

fSortSpecial = strReturn

End If
End Function
 
this code is excately what i need, however i need the function to use two "strDelimit" variables, spitting the string based on two different characters, a "-" and a ".", can anyone help me? thank you.
 
Back
Top