BG Lad said:
I have a query in which one field is text and contains 'WBS' type data,
that
is like "1.2.13.5.7" ... etc.. As you can imagine getting it to sort
correctly is proving difficult. Tried VAL([FieldName]) but that did not
work of course.
Is there a way (function) to remove all of the 'Periods' in one swoop so
that the VAL() function will work?
Bill
I don't know what you mean by "WBS", but when I've seen data that looked
like that in the past, it has represented a hierarchical structure. To sort
such things, I created a function that extracts a specified hierarchical
element from the string:
'----- start of code -----
Function fncHierarchicalElement( _
KeyString As Variant, _
Delimiter As String, _
ByVal ElementNo As Integer) _
As Variant
Dim strKey As String
Dim lngPos As Long
Dim intCount As Integer
If ElementNo < 1 Then
Err.Raise 5
End If
If Len(Delimiter) < 1 Then
Err.Raise 5
End If
fncHierarchicalElement = Null
If IsNull(KeyString) Then
Exit Function
End If
strKey = KeyString
ElementNo = ElementNo - 1
For intCount = 1 To ElementNo
lngPos = InStr(lngPos + 1, strKey, Delimiter, vbBinaryCompare)
If lngPos = 0 Then
If ElementNo = 0 Then
Exit For
Else
Exit Function
End If
End If
Next intCount
strKey = Mid$(strKey, lngPos + 1)
lngPos = InStr(1, strKey, Delimiter, vbBinaryCompare)
If lngPos > 0 Then
strKey = Left$(strKey, lngPos - 1)
End If
fncHierarchicalElement = strKey
End Function
'----- start of code -----
Here's a usage example, it in the Immediate Window:
?fncHierarchicalElement("1.2.13.5.7", ".", 1)
1
?fncHierarchicalElement("1.2.13.5.7", ".", 2)
2
?fncHierarchicalElement("1.2.13.5.7", ".", 3)
13
.... and so on.
You could order a query using SQL similar to this:
SELECT * FROM MyTable
ORDER BY
fncHierarchicalElement([HierarchyField], ".", 1),
fncHierarchicalElement([HierarchyField], ".", 2),
fncHierarchicalElement([HierarchyField], ".", 3),
fncHierarchicalElement([HierarchyField], ".", 4),
fncHierarchicalElement([HierarchyField], ".", 5)
Of course, this is not an efficient query, but it could get the job done. A
table structure in which there's a separate field for each level of
hierarchy would be more efficient, expecially if those fields were indexed.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)