Text field sort question

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

On Fri, 10 Oct 2008 11:51:14 -0700, Brucels wrote:

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

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.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

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.