Solved

Orderby function

Posted on 2016-10-30
12
42 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 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 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 30

Accepted Solution

by:
hnasr earned 250 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 50

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 30

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 30

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 30

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 30

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

726 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