sorting hyphenated numbers

  • Thread starter Thread starter David Leonard
  • Start date Start date
D

David Leonard

A little assistance please. I not sure that "hyphenated" is actually a word,
but anyway....

In my database (Access 2007) I have a field that contains a "hyphenated"
number, i.e.: 09-12-108-1234 for a report or case number. The first two
sections represent the current year and the current month. The third section
is a number assigned to an individual person who will handle the case. The
fourth section is a sequential number for the total number of reports or
cases for the year.

My problem is I do not know how to sort this number so that it will be
sequential for the year, month and sequential number. The number in the
third section will vary with the individual completing the report, i.e.: it
could be for 101 (Jim) or 103 (Bob) or 104 (John), etc.

Can I do a sort based on the three other sections and ignore the second
section and still have the cases sorted sequentially? If I can do this, HOW
can I do this?

Your assistance will be greatly appreciated.
 
David Leonard said:
A little assistance please. I not sure that "hyphenated" is actually a
word,
but anyway....

In my database (Access 2007) I have a field that contains a "hyphenated"
number, i.e.: 09-12-108-1234 for a report or case number. The first two
sections represent the current year and the current month. The third
section
is a number assigned to an individual person who will handle the case.
The
fourth section is a sequential number for the total number of reports or
cases for the year.

My problem is I do not know how to sort this number so that it will be
sequential for the year, month and sequential number. The number in the
third section will vary with the individual completing the report, i.e.:
it
could be for 101 (Jim) or 103 (Bob) or 104 (John), etc.

Can I do a sort based on the three other sections and ignore the second
section and still have the cases sorted sequentially? If I can do this,
HOW
can I do this?
Click to expand...


You could create the following function in a standard module:

'------ start of code ------
Function fncStringElement( _
StringToSplit As Variant, _
Delimiter As String, _
ElementNo As Long) _
As Variant

Static varSplitMe As Variant
Static strDelimiter As Variant
Static astrSplit() As String

If StringToSplit = varSplitMe _
And Delimiter = strDelimiter _
Then
' Do nothing; we've already split this string.
Else
varSplitMe = StringToSplit
strDelimiter = Delimiter
If VarType(varSplitMe) > 1 Then
astrSplit = Split(CStr(varSplitMe), strDelimiter)
End If
End If

fncStringElement = Null
If VarType(varSplitMe) > vbNull Then
On Error Resume Next
fncStringElement = astrSplit(ElementNo)
End If

End Function
'------ end of code ------

Then you could use the function in your query to extract the parts of the
number and sort by them:

SELECT * FROM YourTable
ORDER BY
fncStringElement([CaseNumber], "-", 0),
fncStringElement([CaseNumber], "-", 1),
Val(fncStringElement([CaseNumber], "-", 3))

Depending on whether all the parts of the [CaseNumber] field are consistent
in length, you may not need the Val() function in the last sort field, or
you may need it in the other sort fields; I can't say. For example, if the
last part of the number will always be 4 digits, "0001" to "9999", then you
don't need the Val() function. But if it's not padded with zeros (so it
might be "1", or "10", or "100", for example), then you do need the Val()
function.

Sorting by a function result like this isn't going to be terribly efficient,
I'm afraid, but it may be good enough for your purposes. It's generally
better not to compose keys from multiple meaningful elements, as they then
pose this sort of problem.
 
If the structure of the Case number is always 2 numbers, hyphen, 2 numbers and
then the last four numbers represent the total number for the year

ORDER BY Left([CaseNumber],5) & Right([CaseNumber],4)

In the query design view you would add a calculated field to your query.
== In a field "box" enter
Left([CaseNumber],5) & Right([CaseNumber],4)
== Select your sort order.

You may have to enter
Left([TableName].[CaseNumber],5) & Right([Tablename].[CaseNumber],4)
if you have more than one field named in CaseNumber in the tables used in your
query.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
I only know a tiny fraction of what Dirk does so I tend to look for solutions
down on my level. If all of the "numbers" are the same length (i.e you
have leading zeroes as needed to accomplish that) how 'bout adding a
calculated expression "field" in the query query: (let's call your field
"CaseNum", and the calculated "field" "SortExpression")

SortExpression: left([CaseNum],5)& right([CaseNum],4)

and then sort by SortExpression
 
Fred said:
I only know a tiny fraction of what Dirk does so I tend to look for
solutions
down on my level. If all of the "numbers" are the same length (i.e you
have leading zeroes as needed to accomplish that) how 'bout adding a
calculated expression "field" in the query query: (let's call your field
"CaseNum", and the calculated "field" "SortExpression")

SortExpression: left([CaseNum],5)& right([CaseNum],4)

and then sort by SortExpression
Click to expand...


Sure, that's a fine solution if you can rely on the components of the
"number" to be of consistent lengths.
 
You ought to consider making some small changes to your database and then
you would not have this problem. To start, you need tables that look
something like:
TblCaseHandler
CaseHandlerID
FirstName
LastName
CaseHandlerIDNumber (101, 103, 104)
etc

TblClient
ClientID
etc

TblCase
CaseID
CaseHandlerID
ClientID
CaseDate

In TblCaseHandler, you could consider using CaseHandlerID as the number
assigned to an individual person who will handle the case. The advantage to
this is that it is automatically generated by Access and is guaranteed to be
unique for each case handler. If this is acceptable, you don't need
CaseHandlerIDNumber.

It is not clear what is meant by sequential number for the total number of
reports or cases for the year. Assuming that it is an unique number assigned
to each case, you could consider using CaseID as the number assigned to each
case. The advantage to this is that it is automatically generated by Access
and is guaranteed to be unique for each case.

Now you can create a query that includes the "hyphenated" number. The query
needs to include TblCaseHandler and TblCase and perhaps TblClient. The first
field in the query needs to be:
CaseYear = Year(CaseDate)
and the second field in the query needs to be:
CaseMonth = Month(CaseDate)
Assuming you use CaseHandlerID as the number assigned to an individual
person who will handle the case, CaseHandlerID needs to be the third field
in the query. Assuming you use CaseID as the number assigned to each case,
CaseID needs to be the fourth field in the query. The fifth field in the
query can then be the hyphenated field:
CaseIDNumber = CaseYear & "-" & CaseMonth & "-" & CaseHandlerID & "-" &
CaseID

Now you can easily sort the records in the query the way you want by sorting
CaseYear ascending, sorting CaseMonth ascending sorting CaseID ascending.

Steve
(e-mail address removed)
 
Good thought but that won't work! The sort needs to be on Year, Month and
CaseID. SortField would give a different sort result because CaseHandlerID
is in the middle.

Steve
(e-mail address removed)
 
Thanks to all that responded..... I really appreciate your generous
assistance..... now I just have to experiment with my new ffound knowledge
and my database.

Again, my sincere appreciation !!!
 
This string was extremely helpful on something almost identical that I have confronted -- viz., strings of numbers that the client desires to preserve in their entered form (with hyphens), but also wants to filter in ascending order.

The Val function worked like a charm; it is sure to come in handy with similar data with which I must work.

Much appreciated!

Regards,

q.
 
Back
Top