[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Orderby function

Posted on 2016-10-30
12
Medium Priority
?
51 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

649 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