Derek Brown
asked on
Orderby function
This is a subform but the command buttons are in the subform attached
In a procedure that runs from the attached continuous form. I'm using
Me.RecordSource = "Select * from SetsQuery Order By Val(TrimNumString([DoorNum ber]))"
Where SetsQuery is the forms record source
I copied a function from another thread earlier
I get out of stack space.
What am I doing wrong?
Public Function TrimNumString( _
ByVal strNumString As String, _
Optional ByVal strDecimalChr As String, _
Optional ByVal booAcceptMinus As Boolean) _
As String
' Removes any non-numeric character from strNumString including hexadecimal characters.
' If strDecimalChr is specified, first occurrence of this is not removed.
' If booAcceptMinus is True, a leading or trailing minus sign is accepted.
'
' 1999-08-27. Cactus Data ApS, CPH.
' 2001-06-21. Speed optimized for large string (64 K).
' 2003-12-10. intOffset changed to lngOffset.
Const cbytNeg As Byte = 45 ' "-"
Dim lngPos As Long
Dim lngLen As Long
Dim lngOffset As Long
Dim booDec As Boolean
Dim booNeg As Boolean
Dim bytChr As Byte
Dim bytDec As Byte
Dim strNum As String
strNumString = Trim(strNumString)
lngLen = Len(strNumString)
If lngLen > 0 Then
If Len(strDecimalChr) > 0 Then
bytDec = Asc(strDecimalChr)
End If
' Create empty result string of maximum possible length.
strNum = Space(lngLen)
For lngPos = 1 To lngLen
bytChr = Asc(Mid(strNumString, lngPos, 1))
Select Case bytChr
Case 48 To 57
' Digit.
Case bytDec
' Decimal point.
If booDec = False Then
' One decimal point only.
booDec = True
End If
Case cbytNeg
' Minus sign.
bytChr = 0
If booAcceptMinus = True And booNeg = False Then
If Len(Trim(strNum)) = 0 Or lngPos = lngLen Then
bytChr = cbytNeg
' One minus sign only.
booNeg = True
End If
End If
Case Else
' Ignore any other character.
bytChr = 0
End Select
If bytChr > 0 Then
' Append accepted character by inserting it in result string.
lngOffset = lngOffset + 1
Mid(strNum, lngOffset) = Chr(bytChr)
End If
Next
End If
' Trim and return result string.
TrimNumString Left(strNum, lngOffset)
End Function
Capture41.PNG
In a procedure that runs from the attached continuous form. I'm using
Me.RecordSource = "Select * from SetsQuery Order By Val(TrimNumString([DoorNum
Where SetsQuery is the forms record source
I copied a function from another thread earlier
I get out of stack space.
What am I doing wrong?
Public Function TrimNumString( _
ByVal strNumString As String, _
Optional ByVal strDecimalChr As String, _
Optional ByVal booAcceptMinus As Boolean) _
As String
' Removes any non-numeric character from strNumString including hexadecimal characters.
' If strDecimalChr is specified, first occurrence of this is not removed.
' If booAcceptMinus is True, a leading or trailing minus sign is accepted.
'
' 1999-08-27. Cactus Data ApS, CPH.
' 2001-06-21. Speed optimized for large string (64 K).
' 2003-12-10. intOffset changed to lngOffset.
Const cbytNeg As Byte = 45 ' "-"
Dim lngPos As Long
Dim lngLen As Long
Dim lngOffset As Long
Dim booDec As Boolean
Dim booNeg As Boolean
Dim bytChr As Byte
Dim bytDec As Byte
Dim strNum As String
strNumString = Trim(strNumString)
lngLen = Len(strNumString)
If lngLen > 0 Then
If Len(strDecimalChr) > 0 Then
bytDec = Asc(strDecimalChr)
End If
' Create empty result string of maximum possible length.
strNum = Space(lngLen)
For lngPos = 1 To lngLen
bytChr = Asc(Mid(strNumString, lngPos, 1))
Select Case bytChr
Case 48 To 57
' Digit.
Case bytDec
' Decimal point.
If booDec = False Then
' One decimal point only.
booDec = True
End If
Case cbytNeg
' Minus sign.
bytChr = 0
If booAcceptMinus = True And booNeg = False Then
If Len(Trim(strNum)) = 0 Or lngPos = lngLen Then
bytChr = cbytNeg
' One minus sign only.
booNeg = True
End If
End If
Case Else
' Ignore any other character.
bytChr = 0
End Select
If bytChr > 0 Then
' Append accepted character by inserting it in result string.
lngOffset = lngOffset + 1
Mid(strNum, lngOffset) = Chr(bytChr)
End If
Next
End If
' Trim and return result string.
TrimNumString Left(strNum, lngOffset)
End Function
Capture41.PNG
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I don't think this is going to work Gents. See attached. I would also have to extract the text and order first and then the number part. As you can see we still have a situation where B1.2 comes before B1.12 which it should not. I'm asking for the impossible but thank you all for your effort.
Capture.PNG
Capture.PNG
ASKER
Thanks both
My comment was addressing the stack problem. I suggested creating a new thread for this purpose from a previous thread.
https://www.experts-exchange.com/questions/28979860/OrderBy.html?anchorAnswerId=41866087#a41866087
For sorting issue it can be done, and I gave a solution in other site but was not including decimal number.
I may have a look and modify the query.
https://www.experts-exchange.com/questions/28979860/OrderBy.html?anchorAnswerId=41866087#a41866087
For sorting issue it can be done, and I gave a solution in other site but was not including decimal number.
I may have a look and modify the query.
The sorting can be done without the need for the function, just a simple query.
Assume table a (adesc, ...)
adesc
AA001162
AAAAAAA
AABBBBB
AAZZZZZ
AA12345
B1.2
B1.12
Query:
Assume table a (adesc, ...)
adesc
AA001162
AAAAAAA
AABBBBB
AAZZZZZ
AA12345
B1.2
B1.12
Query:
SELECT adesc
FROM a
ORDER BY IIF(adesc like "*#*", "zzzzzzzz" & adesc, adesc);
Result:
adesc
AAAAAAA
AABBBBB
AAZZZZZ
AA001162
AA12345
B1.12
B1.2
ASKER
I tried that but get the attached. Have I used your suggestion correctly?
SELECT Item.ItemNumber, Item.DoorNumbers, TblTemp1.DN
FROM Item INNER JOIN TblTemp1 ON Item.ItemNumber = TblTemp1.Item_ItemNumber
ORDER BY IIf(TblTemp1.DN Like "*#*","zzzzzzzz" & TblTemp1.DN,TblTemp1.DN);
Using this I get the attached
The problem here I think is that a human intuitively knows the order but in my situation a customer can enter door numbers with any alpha numeric combination, inc decimal points anywhere. So writing code or a function may be too much
Capture42.PNG
SELECT Item.ItemNumber, Item.DoorNumbers, TblTemp1.DN
FROM Item INNER JOIN TblTemp1 ON Item.ItemNumber = TblTemp1.Item_ItemNumber
ORDER BY IIf(TblTemp1.DN Like "*#*","zzzzzzzz" & TblTemp1.DN,TblTemp1.DN);
Using this I get the attached
The problem here I think is that a human intuitively knows the order but in my situation a customer can enter door numbers with any alpha numeric combination, inc decimal points anywhere. So writing code or a function may be too much
Capture42.PNG
Please note, the following:
Database design is very important and should be treated with importance to simplify reporting.
Database design is very important and should be treated with importance to simplify reporting.
a customer can enter door numbers with any alpha numeric combination, inc decimal points anywhereYou may need to standardize the coding in more than one column to avoid extra work to decipher the codes when querying the database.
ASKER
Hi Hnasr
Yes I do see that but my customers have to use their customers door numbers. So we do not have any control over how the door numbers could be structured. It's the UK construction industry virtually nothing is standardised. I was just trying to mitigate the glaringly obvious ones where access puts 10 before 2
Thanks again
Derek
Yes I do see that but my customers have to use their customers door numbers. So we do not have any control over how the door numbers could be structured. It's the UK construction industry virtually nothing is standardised. I was just trying to mitigate the glaringly obvious ones where access puts 10 before 2
Thanks again
Derek
Yes I do see that but my customers have to use their customers door numbersI tried to work on this.
If still in need for it, then start a new thread asking just that. Displaying the original data and the output required.
I developed a module to achieve that.
Example: I have data in a field like this:
adesc
AA001162
AAAAAAA
AABBBBB
AAZZZZZ
AA12345
B1.2
B1.12
C10
C2
D10
D2
I want it to be sorted as this:
adesc
AA001162
AA12345
AAAAAAA
AABBBBB
AAZZZZZ
B1.2
B1.12
C2
C10
D2
D10
When opening a new thread, alert us by adding, here, a link to the new thread.
ASKER
Hi Hnasr
Sorry took so long coming back.
New question
https://www.experts-exchange.com/questions/28981306/Correct-ordering.html
Sorry took so long coming back.
New question
https://www.experts-exchange.com/questions/28981306/Correct-ordering.html
Here's the original - just in case:
Open in new window
/gustav