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 "Val" in the line mentioned above, it came back with a "Run-time error 9" "Subscript out of range." When I go to the debugger nothing really comes out of it except when I hold the mouse over "varArray" it says "varArray(intReturnPlace - 1) = <subscript out of range>". Mouse over "basSplitString" it says "basSplitString = 0". Mouse over "intReturnPlace - 1" and it says "intReturnPlace = 2".
This is the code in the query:
This is the code in its own module named "PartNumberSplit"
When I run the new code, it at first gave me a typemismatch error, so I tried to put the "Val" expression in there. Now the error I get when I try to open the query is "Compile Error. in query expression 'basSplitString([TblDrawing]![PartNumber],1'."
This is the code in the query:
The same is done for the three. I noticed that when I opened the query in design view it got rid of "FirstSort:" 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 "FirstSort:" again.
This is the code in its own module named "PartNumberSplit"
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
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 "Val" in the line mentioned above, it came back with a "Run-time error 9" "Subscript out of range." When I go to the debugger nothing really comes out of it except when I hold the mouse over "varArray" it says "varArray(intReturnPlace - 1) = <subscript out of range>". Mouse over "basSplitString" it says "basSplitString = 0". Mouse over "intReturnPlace - 1" and it says "intReturnPlace = 2".
This is the code in the query:
Code:
basSplitString([TblDrawing]![PartNumber],1)
This is the code in its own module named "PartNumberSplit"
Code:
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 "Val" expression in there. Now the error I get when I try to open the query is "Compile Error. in query expression 'basSplitString([TblDrawing]![PartNumber],1'."
This is the code in the query:
Code:
basSplitString([TblDrawing]![PartNumber],1)
This is the code in its own module named "PartNumberSplit"
Code:
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