Getting the Sort Order on a string with multiple "Periods"

B

BG Lad

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
 
B

BG Lad

Of course that is only part of the answer due to the number of characters
changing between the 'Periods', I still need to left-zero-pad between the
periods first. I know I'll get there but this will be really tricky in the
end.
Nice just to vent it, sorry about taking up space here.
Bill
--
B Good Lad


BG Lad said:
Hey! Guess what I found (all by myself) the Replace() function. This must be
something new as I don't recall it in previous versions.
Bill
--
B Good Lad


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
 
D

Dirk Goldgar

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.
 
D

Dirk Goldgar

BG Lad said:
Hey! Guess what I found (all by myself) the Replace() function. This must
be
something new as I don't recall it in previous versions.


The Replace() function was added in Access 2000, though I believe it took a
service pack to make it directly callable from a query.
 
B

BG Lad

Thanks Dirk,

Yes the WBS stands for "work breakdown structure" that is a hierarchal
representation of the sequencing of work processes (I'm sort of sure about
that wording)

I will recode to specifically use the period ("."). I'm sure it'll get me by
this bit-of-a-nightmare.

Appreciate the sharing; I'll credit you in the code so you'll live forever.
Bill
--
B Good Lad

Dirk Goldgar said:
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)
 
B

BG Lad

It worked with the change;
fncHierarchicalElement = "000" (at Initialize)

It worked but is too slow for users, I think I'll have to make it a single
call with the field data and have the function parse and pad it.

You can be sure you put me on a good track!
Thanks again Dirk. (What a guy!)
--
B Good Lad


BG Lad said:
Thanks Dirk,

Yes the WBS stands for "work breakdown structure" that is a hierarchal
representation of the sequencing of work processes (I'm sort of sure about
that wording)

I will recode to specifically use the period ("."). I'm sure it'll get me by
this bit-of-a-nightmare.

Appreciate the sharing; I'll credit you in the code so you'll live forever.
Bill
--
B Good Lad

Dirk Goldgar said:
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)
 
B

BG Lad

Oops, also used;
fncHierarchicalElement = Format(strKey, "000") right at the end to Lpad the
string
Bill
--
B Good Lad


BG Lad said:
Thanks Dirk,

Yes the WBS stands for "work breakdown structure" that is a hierarchal
representation of the sequencing of work processes (I'm sort of sure about
that wording)

I will recode to specifically use the period ("."). I'm sure it'll get me by
this bit-of-a-nightmare.

Appreciate the sharing; I'll credit you in the code so you'll live forever.
Bill
--
B Good Lad

Dirk Goldgar said:
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top