Making numbers in text behave like numbers...

[waldo7474]

Hi All!!

I have a little problem that I can't seem to get around. I am using part numbers in my database. These part numbers have dashes in them. Because the part numbers have dashes in them, I must use a text field, however when I try to organize them in ascending order they go by the first number, not the whole number before the dash. In other words a part number like 11-3-23, would come before 4-2-22 because the first number is a one (and not eleven).

Do you know of any way that I can get over this obstacle, so that the numbers between the dashes can be treated like numbers instead of text? I would love to put imaginary zeros in front of the numbers between the dashes (this would solve the problem), but I don't know how, and I don't want to ever see them. If it matters, the first number can be one or two digits, the second number can be one or two digits, and the third number can have any where from one to four digits.

Thanks for any help in advance!!
Wally

 

[jebry]

Hi!

You can do this in a query by adding fields that call a function to return the numbers separately. If you are in A2k or higher you should have the Split function available to you and then you pass which part you want returned (1,2 or 3) along with the field. If you are working with an earlier version, I can give you a function to do the same thing. You can sort on the fields without showing them.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[waldo7474]

Thanks for your quick response Jeff.

I understand where you are going with the split idea. It sounds like it would work. I have never heard of it before and I don't know how to go about doing what you are saying. I put "split field" in to help, and it was a far cry from help. It didn't really know what I was looking for. I do have Access 2000. However, I need a little more instruction on how to carry out your plan of attack.

Just to be clear, if I split up each of these three sections, it will take the whole section and not just the first "letter". I need it to understand that 11 is higher than 5. If this will do it, and you can show me how I would be most grateful!!

Thanks again for your help!!
Wally

 

[VicM]

Wally:

If all your part nos. are in the same format, ####0-0-00, where the # represents a number or nothing, and the 0 represents a required number, then you might do better to save the part nos. as Number and not text. You can set the Format property of the textbox to 0-0-00.

The user type in the part no. without the dashes, and when he/she moves to another control on the form or another record, the number will appear with the dashes.

Then you can sort on the number field without a problem.

If the part no. format isn't always the same, then jebry's suggestion might help you.

HTH,

Vic

 

[jebry]

Hi Wally!

Put this function in a module:

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

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

End Function

If the field you send is null then this function will return a -1 in all three spots. This will bring all of the empty fields to the top. If this is undesirable then return 10000 in each spot and they will all be at the end.

In your query design view add these fields:

FirstSort: basSplitString([YourTable]![YourField], 1)
SecondSort: basSplitString([YourTable]![YourField], 2)
ThirdSort: basSplitString([YourTable]![YourField], 3)

Unclick the show and set the sort to ascending and you should get what you are looking for.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[waldo7474]

Thank you both again for your puick responses. I have tried Jeff's suggestion because the dashes don't always belong in the same spot.

Here is my problem. When I run the query I get a type-mismatch error in the public function that I wrote.

When the debugger runs this is the line that is highlighted:

basSplitString = CInt(varArray(intReturnPlace - 1))

Any more thoughts Jeff. I am about to hand a big star to you. This looks like it should work. Thanks for all your help.

For more help: when I put the mouse over "basSplitString" it says "basSplitString=0". Mouse over "CInt" it says "CInt (varArray(intReturnPlace - 1))>Type mismatch". Mouse over "varArray" it says "varArray(intReturnPlace - 1) = "80.." ". Mouse over "intReturnPlace", it says "intReturnPlace = 1".

I hope that makes sense to you and helps.

Thanks again for your time,
Wally

 

[jebry]

Hi Wally!

It looks like the Split function is returning a couple extra characters. I'm guessing that it automatically returns strings of the same length (though I can't see why). Anyway, we can write our own split function easy enough:

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, "-&quot
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(strArray(intReturnPlace))
Else
basSplitString = -1
End If

End Function

Hopefully this will work.

Jeff Bridgham
bridgham@purdue.edu

 

[VicM]

Wally:

Don't know what the two dots after the 80 represent, but you might want to try this construction:

basSplitString = CInt(Val(varArray(intReturnPlace - 1)))

The Val function converts text number to a number, but stops at first non numeric character. So if text number in varArray(intReturnPlace - 1) is always at the start of the string, then this should do the trick.

And if there are spaces before the number in the string, you can use the Trim function.

HTH,

Vic

 

[jebry]

Hi Vic!

I thought that those dots were his problem also. I didn't think about the Val function, that might well work and be an easier fix than mine! Thanks.

Jeff Bridgham
bridgham@purdue.edu

 

[waldo7474]

Thanks for your help guys. I tried both 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 this 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

I hope this helps you guys in determining what I am doing wrong. It might also help to reiterate what I am trying to do. There are three different numbers inside my one part number. They normally look like this &quot;45-3-786&quot; I want Access to understand that they are three different numbers to sort by. The first, then second, then third. I think we are all on the same track, I just wanted to make sure. BTW, what does this &quot;intReturnPlace - 1&quot; mean?

Thank you guys so much for all your help. I feel like we're so close.
Wally