Solved

Orderby function

Posted on 2016-10-30
12
31 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 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
Comment Utility
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 30

Accepted Solution

by:
hnasr earned 250 total points
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
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
 

Author Comment

by:DatabaseDek
Comment Utility
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
Comment Utility
Thanks both
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 30

Expert Comment

by:hnasr
Comment Utility
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
Comment Utility
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 30

Expert Comment

by:hnasr
Comment Utility
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
Comment Utility
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 30

Expert Comment

by:hnasr
Comment Utility
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
Comment Utility
Hi Hnasr

Sorry took so long coming back.

New question

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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now