?
Solved

Orderby function

Posted on 2016-10-30
12
Medium Priority
?
56 Views
Last Modified: 2016-11-06
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([DoorNumber]))"

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
0
Comment
Question by:DatabaseDek
  • 5
  • 5
  • 2
12 Comments
 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 1000 total points
ID: 41866222
Try creating a query:

    Select *, Val(TrimNumString([DoorNumber])) As NumDoor
    From SetsQuery

Save it as, say, SetsQuerySort.

Then use:

    Me.RecordSource = "Select * From SetsQuerySort Order By NumDoor"

/gustav
0
 
LVL 31

Accepted Solution

by:
hnasr earned 1000 total points
ID: 41866343
Looks like you missed an equal sign to return the function value. The function is calling itself leading to a stack problem
Modify:
 ' Trim and return result string.
  TrimNumString Left(strNum, lngOffset)
End Function

To:
 ' Trim and return result string.
  TrimNumString = Left(strNum, lngOffset)
End Function

If issue persists, upload a sample database to debug.
1
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 41866500
Good catch!

Here's the original - just in case:
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

Open in new window

/gustav
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 

Author Comment

by:DatabaseDek
ID: 41866735
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
0
 

Author Closing Comment

by:DatabaseDek
ID: 41866736
Thanks both
0
 
LVL 31

Expert Comment

by:hnasr
ID: 41866937
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#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.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 41866981
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:
SELECT adesc
FROM a
ORDER BY IIF(adesc like "*#*", "zzzzzzzz" & adesc, adesc);

Result:
adesc
AAAAAAA
AABBBBB
AAZZZZZ
AA001162
AA12345
B1.12
B1.2

Open in new window

0
 

Author Comment

by:DatabaseDek
ID: 41867276
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
0
 
LVL 31

Expert Comment

by:hnasr
ID: 41867346
Please note, the following:
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 anywhere
You may need to standardize the coding in more than one column to avoid extra work to decipher the codes when querying the database.
0
 

Author Comment

by:DatabaseDek
ID: 41867394
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
0
 
LVL 31

Expert Comment

by:hnasr
ID: 41871046
Yes I do see that but my customers have to use their customers door numbers
I 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.
0
 

Author Comment

by:DatabaseDek
ID: 41876086
Hi Hnasr

Sorry took so long coming back.

New question

https://www.experts-exchange.com/questions/28981306/Correct-ordering.html
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question