Prev: Possible Report Issue
Next: Acess DB - list of worldwide contacts - can I show them on a m
From: David Leonard on 10 Dec 2009 19:35
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.
From: Dirk Goldgar on 11 Dec 2009 01:29
"David Leonard" <DavidLeonard(a)discussions.microsoft.com> wrote in message
news:2B146CD7-401F-4CB2-AE68-602924ED3350(a)microsoft.com...
>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?


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.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

From: John Spencer on 11 Dec 2009 08:40
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

David Leonard wrote:
> 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.
From: Fred on 11 Dec 2009 09:03
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

From: Dirk Goldgar on 11 Dec 2009 10:47
"Fred" <Fred(a)discussions.microsoft.com> wrote in message
news:2422D5E1-BB38-4B3A-A39E-08173CF365C4(a)microsoft.com...
>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


Sure, that's a fine solution if you can rely on the components of the
"number" to be of consistent lengths.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 |  Next  |  Last
Pages: 1 2
Prev: Possible Report Issue
Next: Acess DB - list of worldwide contacts - can I show them on a m