Making numbers in text behave like numbers...

[waldo7474]

Hi All!!!

I had posted this in the "forms" forum previously and got some help, but had some problems, and got no responses. I was hoping someone in here could help.

I have a part number that in a table, it is a "text" field because I have hyphens in it. There are three different numbers inside my one part number. They normally look like this "45-3-786" I want Access to understand that they are three different numbers to sort by. The first, then second, then third. I want Access to understand that they're numbers. Right now, they are sorted such that 45-... comes before 9-...

I tried two methods and still have errors. I don't think I am doing anything wrong, but I must be or else I wouldn't get errors. I am going to try to copy the things out of my database so you can see if I have typed anything wrong. I should probably state that each time I put in the public function (I tried them in two seperate (but copied) databases, I copied the code from a thread and pasted it into its own new module.

When I put the &quot;Val&quot; in the line mentioned above, it came back with a &quot;Run-time error 9&quot; &quot;Subscript out of range.&quot; When I go to the debugger nothing really comes out of it except when I hold the mouse over &quot;varArray&quot; it says &quot;varArray(intReturnPlace - 1) = <subscript out of range>&quot;. Mouse over &quot;basSplitString&quot; it says &quot;basSplitString = 0&quot;. Mouse over &quot;intReturnPlace - 1&quot; and it says &quot;intReturnPlace = 2&quot;.

This is the code in the query:

basSplitString([TblDrawing]![PartNumber],1)

This is the code in its own module named &quot;PartNumberSplit&quot;

Public Function basSplitString(varField As Variant, intReturnPlace As Integer) As Integer

   Dim varArray As Variant
   If IsNull(varField) = False Then
      varArray = Split(varField, "-")
      basSplitString = CInt(Val(varArray(intReturnPlace - 1)))
   Else
      basSplitString = 10000
   End If

End Function

When I run the new code, it at first gave me a typemismatch error, so I tried to put the &quot;Val&quot; expression in there. Now the error I get when I try to open the query is &quot;Compile Error. in query expression 'basSplitString([TblDrawing]![PartNumber],1'.&quot;

This is the code in the query:

basSplitString([TblDrawing]![PartNumber],1)

The same is done for the three. I noticed that when I opened the query in design view it got rid of &quot;FirstSort:&quot; at the beginning of this code, so I put it back in, saved it, closed the query. Tried to run it again and it gave the same error. Back in design view it deleted the &quot;FirstSort:&quot; again.

This is the code in its own module named &quot;PartNumberSplit&quot;

Public Function basSplitString(varField As Variant, intReturnPlace As Integer) As Integer

   Dim strArray(1 To 3) As String
   Dim strField As String
   Dim lngDash As Long
   Dim intPlace As Integer

   If IsNull(varField) = False Then
      strField = varField
      For intPlace = 1 To 3
         lngDash = InStr(strField, "-")
         If lngDash = 0 Then
            strArray(intPlace) = strField
         Else
            strArray(intPlace) = Left(strField, lngDash - 1)
            strField = Mid(strField, lngDash + 1)
         End If
      Next intPlace
      basSplitString = CInt(Var(strArray(intReturnPlace)))
   Else
      basSplitString = -1
   End If

End Function

Hopefully this all makes sense. If you need any further clarification I will be glad to give it. I am totally stumped and would love any assistance given.

Thank you in advance for all your help.
Wally

 

[beetee]

' pull a number from a string, from startsearch to next delim pos
Function PullNumber(StartSearch As Integer, Str As String, Delim As String, Done As Boolean) As Integer
Dim DelimPos As Integer

DelimPos = InStr(StartSearch, Str, Delim)

If DelimPos = 0 Then
Done = True
DelimPos = Len(Str) + 1
End If
PullNumber = Int(Mid(Str, StartSearch, DelimPos - StartSearch))
StartSearch = DelimPos + 1
End Function

' compares text strings as numbers
' e.g. 111-22-333 and 111-22-34 will compare so that the 111-22-333 is > 111-22-34
' individual numbers are broken up by Delim
' since we want 11-11-11 to be more different from 9-11-11 than from 11-11-9, the segcount
' parameter is used to generate a multiplier value. For three number segments, supply 3 as the segcount
Function CompareTextDataAsNumber(ByVal Str1 As String, ByVal Str2 As String, Delim As String, SegCount As Integer) As Double
Dim SegMult As Double
SegMult = 1#

Dim counter As Integer
For counter = 1 To SegCount - 1
SegMult = SegMult * 10
Next counter

Dim RetVal As Double
RetVal = 0

Dim StartSearch1 As Integer
StartSearch1 = 1

Dim StartSearch2 As Integer
StartSearch2 = 1

Dim Done As Boolean
Done = False

Do While Not Done
Dim Val1 As Integer
Val1 = PullNumber(StartSearch1, Str1, Delim, Done)

Dim Val2 As Integer
Val2 = PullNumber(StartSearch2, Str2, Delim, Done)

RetVal = (Val1 - Val2) * SegMult
If Done Or RetVal <> 0 Then Exit Do
SegMult = SegMult / 10
Loop
CompareTextDataAsNumber = RetVal
End Function

Sub MyTestIt()
Dim MyArray(0 To 5) As String
MyArray(0) = &quot;111-222-333&quot;
MyArray(1) = &quot;111-222-334&quot;
MyArray(2) = &quot;11-22-33&quot;
MyArray(3) = &quot;111-222-333&quot;
MyArray(4) = &quot;19-19-19&quot;
MyArray(5) = &quot;9-9-9&quot;

Dim counter As Integer
For counter = 0 To 2
Dim CurIx As Integer
CurIx = counter * 2
Debug.Print MyArray(CurIx) & &quot;, &quot; & MyArray(CurIx + 1) & &quot; = &quot; & CompareTextDataAsNumber(MyArray(CurIx), MyArray(CurIx + 1), &quot;-&quot;, 3)
Next counter
End Sub

 

[beetee]

You know, I just realized that the SegCount thing isn't sufficent. We need to multiply each segment by a number large enough for the largest segment, e.g.

Therefore, the following change should be made:
SegMult = SegMult * 10
should be changed to
SegMult = SegMult * LargestSegment

LargestSegment should be added as a parameter next to SegCount

 

[waldo7474]

Hi Beetee!!

Thanks for the quick response. I have come across a couple of questions while I look at your response.

I want to get the numbers in sequence based on a query. I tried to write an expression, and I am missing an argument. I do not know what to put there. This is what I have in my query:

Seperate1: CompareTextDataAsNumber ([PartNumber],??,"-",3)

I don't know what to put in place of the question marks. I also didn't know where to put the part that you added at the end. The LargestSegment I put below the function, not inside. I put it in the line above SegMult, and as an Integer. I hope this is correct.

I am not totally sure as to what you are doing with all the code. I am pretty &quot;green&quot; when it comes to visual basic. I can usually decipher what someone is trying to do, but in this case I can't.

How can I run your sub program? I go to run, it picks your sub program, and then nothing happens. I guess this is better than an error, but I am curious as to how I would go about running it, to see results.

Can I put this function in a query? Am I close to how to do it? I put this code in its own module. I am assuming that it is what I was supposed to do. When I ran the query it did look for the function and it seemed to be working, I just wasn't sure how to put the variables in the function.

I hope all this makes sense. I really appreciate your time and effort that you have put in so far on me. I hope that you can answer these last couple of questions for me, and get me up and running.

Thanks again!!
Wally

 

[beetee]

OK, here we go...

First, Here's the new, &quot;improved&quot; function to use:

' compares text strings as numbers
' e.g. 111-22-333 and 111-22-34 will compare so that the 111-22-333 is > 111-22-34
' individual numbers are broken up by Delim
' since we want 11-11-11 to be more different from 9-11-11 than from 11-11-9, the segcount
' parameter is used to generate a multiplier value. For three number segments, supply 3 as the segcount
Function CompareTextDataAsNumber(ByVal Str1 As String, ByVal Str2 As String, Delim As String, SegCount As Integer, LargestSegment As Double) As Double
Dim SegMult As Double
SegMult = 1#

Dim counter As Integer
For counter = 1 To SegCount - 1
SegMult = SegMult * LargestSegment
Next counter

Dim RetVal As Double
RetVal = 0

Dim StartSearch1 As Integer
StartSearch1 = 1

Dim StartSearch2 As Integer
StartSearch2 = 1

Dim Done As Boolean
Done = False

Do While Not Done
Dim Val1 As Integer
Val1 = PullNumber(StartSearch1, Str1, Delim, Done)

Dim Val2 As Integer
Val2 = PullNumber(StartSearch2, Str2, Delim, Done)

RetVal = (Val1 - Val2) * SegMult
If Done Or RetVal <> 0 Then Exit Do
SegMult = SegMult / LargestSegment
Loop
CompareTextDataAsNumber = RetVal
End Function

-------------------------------------------------

Seperate1: CompareTextDataAsNumber ([PartNumber],??,&quot;-&quot;,3)

this function requires two part numbers to compare, so you will want to do something like:

Seperate1: CompareTextDataAsNumber ([PartNumber],[SomeOtherPartNumber],&quot;-&quot;,3, 1000)

To test the subprogram, this routine (which I included earlier), should work:

Sub MyTestIt()
Dim MyArray(0 To 5) As String
MyArray(0) = &quot;111-222-333&quot;
MyArray(1) = &quot;111-222-334&quot;
MyArray(2) = &quot;11-22-33&quot;
MyArray(3) = &quot;111-222-333&quot;
MyArray(4) = &quot;19-19-19&quot;
MyArray(5) = &quot;9-9-9&quot;

Dim counter As Integer
For counter = 0 To 2
Dim CurIx As Integer
CurIx = counter * 2
Debug.Print MyArray(CurIx) & &quot;, &quot; & MyArray(CurIx + 1) & &quot; = &quot; & CompareTextDataAsNumber(MyArray(CurIx), MyArray(CurIx + 1), &quot;-&quot;, 3, 1000)
Next counter
End Sub

------------------------------
note that I added that 1000 at the end of the function call.

You can also try it from the 'immediate' window. Open a module (code window, whateer, I use 97 and they changed it for Access 2000), then press ^G to display the immediate window.

Type this in the immediate window:
print CompareTextDataAsNumber(&quot;11-22&quot;, &quot;11-9&quot;, &quot;-&quot;, 2, 100)
you should see 13 appear in the window
print CompareTextDataAsNumber(&quot;22-11&quot;, &quot;9-97&quot;, &quot;-&quot;, 2, 100)
will display 1300

You can only put the function in a query if you have two part numbers to compare. Therefore, if you want to be able to sort by part number, we may have a bit of a problem.

In fact, to solve this problem, we have to change the part numbers into numbers much the same way the program compares them.

Then, what you could do is create a query that uses the conversion function, then sort by the converted number. Here is the conversion function:

Function ConvertSegmentedTextToNumber(ByVal Str As String, Delim As String, SegCount As Integer, LargestSegment As Double) As Double
Dim SegMult As Double
SegMult = 1#

Dim counter As Integer
For counter = 1 To SegCount - 1
SegMult = SegMult * LargestSegment
Next counter

Dim RetVal As Double
RetVal = 0

Dim StartSearch As Integer
StartSearch = 1

Dim Done As Boolean
Done = False

Do While Not Done
RetVal = RetVal + PullNumber(StartSearch, Str, Delim, Done) * SegMult
SegMult = SegMult / LargestSegment
Loop
ConvertSegmentedTextToNumber = RetVal
End Function

------------------
here's how you can call it in a query:
SortVal:ConvertSegmentedTextToNumber([PartNumber], &quot;-&quot;, 3, 1000)

the segment multiplier is the largest value you expect to find between the '-'. The number of segments is the number of dashes plus one.

If you have alpha data in your part numbers, this code wont work unless we switch to base 36.

 

[gnosis13]

Just another way...

In the query design view add these expressions:

Expr1: Len([PartNumber])
Expr2: Left([PartNumber],InStr([PartNumber],&quot;-&quot-1)
Expr3: Right([PartNumber],[Expr1]-InStr([PartNumber],&quot;-&quot)
Expr4: Left([Expr3],InStr([PartNumber],&quot;-&quot-2)
Expr5: Len([Expr4])+2
Expr6: Right([PartNumber],[Expr5])

Expr2, Expr4, & Expr6 will be your part numbers seperated out as columns (this gets close but needs some tweeken...my math is off a little). There are also other functions you could use like mid() or InStr()...I just stuck these in as example.

This will allow you to sort the query based on each individual section of the part number.

Sorry I don't have time to correct the code...time to go home... A+, N+, MCP

 

[waldo7474]

Thank you both for your quick responses.

I tried both solutions. And unfortunately there are letters in some of these part numbers and to make it even more complicated, sometimes there are more than two dashes. Why do people do this to me?

So BeeTee, your code won't work at this time because there is text involved. I also have slashes if that makes any difference. Maybe you have another solution for me. I am crossing my fingers.

Also Choctaw, this seems like an easier solution. However a couple of problems arise with this too. I sometimes have more than two dashes, as I said earlier. Also, (and more importantly) it will not sort with this set up. As soon as I ask it to sort, it asks for &quot;Expr3&quot;. It worked before. I think it is trying to seperate the records or something.

I really appreciate your input. Sorry Beetee, I didn't tell you my application first, I figured it would be easy after I got Access to think of them as numbers. Assuming gets me nowhere. I will learn this some day.

Thanks for all your help you two. Hopefully you guys can get me out of this hole that I have apparently dug for myself.

Thanks again!!
Wally

 

[gnosis13]

This is one of those seeming simple problems that has a very complex solution....lots of fun! Good Luck! A+, N+, MCP

 

[beetee]

The reason I adopted the solution I did was for efficiency, making only one pass throught the text fields, rather than multiple calls to mid, instr, etc. However, speed may not be the issue here and the simpler solution is indeed the better one.

Regarding changing to base 36 to handle alpha characters, there's a simple solution (using little more than grade-school math). Once the base 36 calculation is made, you apply the change to the 'pull number' subroutine, as follows:

Function MakeBase36Number(TheText As String) As Double
Dim TextLen As Integer
TextLen = Len(TheText)

Dim counter As Integer
Dim RetVal As Double
RetVal = 0
For counter = 1 To TextLen
Dim CurChar As String
CurChar = Mid(TheText, counter, 1)
Dim CurValue As Integer
If (CurChar >= &quot;0&quot; And CurChar <= &quot;9&quot Then
CurValue = Asc(CurChar) - Asc(&quot;0&quot
ElseIf (CurChar >= &quot;A&quot; And CurChar <= &quot;Z&quot Then
' note: the Not 32 bit maps lower case to upper case
CurValue = (Asc(CurChar) And (Not 32)) - Asc(&quot;A&quot + 10
Else
CurValue = 0
End If
RetVal = 36 * RetVal + CurValue
Next counter
MakeBase36Number = RetVal
End Function

Function PullNumber(StartSearch As Integer, Str As String, Delim As String, Done As Boolean) As Integer
Dim DelimPos As Integer

DelimPos = InStr(StartSearch, Str, Delim)

If DelimPos = 0 Then
Done = True
DelimPos = Len(Str) + 1
End If
PullNumber = MakeBase36Number(Mid(Str, StartSearch, DelimPos - StartSearch))
StartSearch = DelimPos + 1
End Function

 

[gnosis13]

Actually beetee, I like your solution. Thats a nice function you have there! The addition info Walgo7474 gave complicates matters but your functions are the route to take. I hope you don't mind if I copy this for studying...good stuff! A+, N+, MCP

 

[beetee]

a much simpler solution just occured to me. And, one much likelier to return a correct answer, since you may run out of significant digits when comparing something like

aaaaaaaaaaa-aaaaaaaaaaaaaa-aaaaaaaaaaaaaaa
to
aaaaaaaaaaa-aaaaaaaaaaaaaa-aaaaaaaaaaaaaab

After pulling substrings, simply check the length of the substrings first. The longer substring is always greater.
If they are the same length, simply return a string compare.

the code would be:

Function PullSubstring(StartSearch As Integer, Str As String, Delim As String, Done As Boolean) As String
Dim DelimPos As Integer

DelimPos = InStr(StartSearch, Str, Delim)

If DelimPos = 0 Then
Done = True
DelimPos = Len(Str) + 1
End If
PullSubstring = Mid(Str, StartSearch, DelimPos - StartSearch)
StartSearch = DelimPos + 1
End Function

Function SegmentedStringCompare(ByVal Str1 As String, ByVal Str2 As String, Delim As String, SegCount As Integer, LargestSegment As Double) As Integer
Dim RetVal As Integer

Dim StartSearch1 As Integer
StartSearch1 = 1

Dim StartSearch2 As Integer
StartSearch2 = 1

Dim Done As Boolean
Done = False

Do While Not Done
Dim Val1 As String
Val1 = PullSubstring(StartSearch1, Str1, Delim, Done)
Dim Val1Len As Integer
Val1Len = Len(Val1)

Dim Val2 As String
Val2 = PullSubstring(StartSearch2, Str2, Delim, Done)
Dim Val2Len As Integer
Val2Len = Len(Val2)

If Val1Len > Val2Len Then
RetVal = 1
ElseIf Val2Len > Val1Len Then
RetVal = -1
Else
RetVal = StrComp(Val1, Val2, vbTextCompare)
End If
If (RetVal <> 0) Then Exit Do
Loop
SegmentedStringCompare = RetVal
End Function

Sub MyTestIt()
Dim MyArray(0 To 13) As String
MyArray(0) = &quot;111-222-333&quot;
MyArray(1) = &quot;111-222-334&quot;
MyArray(2) = &quot;11-22-33&quot;
MyArray(3) = &quot;111-222-333&quot;
MyArray(4) = &quot;19-19-19&quot;
MyArray(5) = &quot;9-9-9&quot;
MyArray(6) = &quot;111-222-333&quot;
MyArray(7) = &quot;111-222-33a&quot;
MyArray(8) = &quot;111-222-333&quot;
MyArray(9) = &quot;111-22a-333&quot;
MyArray(10) = &quot;a11-222-333&quot;
MyArray(11) = &quot;111-222-333&quot;
MyArray(12) = &quot;111-222-333&quot;
MyArray(13) = &quot;111-222-333&quot;

Dim counter As Integer
For counter = 0 To 6
Dim CurIx As Integer
CurIx = counter * 2
Debug.Print MyArray(CurIx) & &quot;, &quot; & MyArray(CurIx + 1) & &quot; = &quot; & SegmentedStringCompare(MyArray(CurIx), MyArray(CurIx + 1), &quot;-&quot;, 3, 1000)
Next counter
End Sub



The only problem with this approach is that if you're trying to generate a column of sortable values, it won't work.