If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
sorting hyphenated numbers
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. |
#2
|
|||
|
|||
sorting hyphenated numbers
"David Leonard" wrote in message
... 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) |
#3
|
|||
|
|||
sorting hyphenated numbers
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. |
#4
|
|||
|
|||
sorting hyphenated numbers
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 |
#5
|
|||
|
|||
sorting hyphenated numbers
"Fred" wrote in message
... 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) |
#6
|
|||
|
|||
sorting hyphenated numbers
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 "David Leonard" wrote in message ... 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. |
#7
|
|||
|
|||
sorting hyphenated numbers
How about a calculated field in the query
SortField: Replace([HyphenatedField],"-","") then sort on the field SortField? -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200912/1 |
#8
|
|||
|
|||
sorting hyphenated numbers
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 "Linq Adams via AccessMonster.com" u28780@uwe wrote in message news:a07430045d204@uwe... How about a calculated field in the query SortField: Replace([HyphenatedField],"-","") then sort on the field SortField? -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200912/1 |
#9
|
|||
|
|||
sorting hyphenated numbers
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 !!! "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. |
Thread Tools | |
Display Modes | |
|
|