Sacha Walter
asked on
Access 2010 Function - problem with InStr function
Hi Experts,
I am trying to put 3 functions together (InStr, Left, Len) to do the following:
Take the value from a text field that contains text such as: AAA-B-END, BB-C-END, D-K-END
And return the number of characters before the first hypen. The examples above would equate to: 3 (AAA =3 characters), 2 (BB=2 characters) , 1 (D=1 character)
In the function I tried to build below, the first case works, but the second case always returns 4 when it should return the 3, 2, 1 examples above. I believe the InStr portion of the function is reading as 0, but I can't figure out how to make it work to output the position of the character before the first "-" in the VPCode......
Function Business_Division_Number(V PCode As String)
Dim BusDivNum As Double
Select Case VPCode
Case Is = "B-E-END"
BusDivNum = 2
Case Is = Len(Left(VPCode, (InStr(1, VPCode, "-", vbTextCompare) - 1))) = BusDivNum
Case Else
BusDivNum = 4
End Select
Business_Division_Number = BusDivNum
End Function
I am trying to put 3 functions together (InStr, Left, Len) to do the following:
Take the value from a text field that contains text such as: AAA-B-END, BB-C-END, D-K-END
And return the number of characters before the first hypen. The examples above would equate to: 3 (AAA =3 characters), 2 (BB=2 characters) , 1 (D=1 character)
In the function I tried to build below, the first case works, but the second case always returns 4 when it should return the 3, 2, 1 examples above. I believe the InStr portion of the function is reading as 0, but I can't figure out how to make it work to output the position of the character before the first "-" in the VPCode......
Function Business_Division_Number(V
Dim BusDivNum As Double
Select Case VPCode
Case Is = "B-E-END"
BusDivNum = 2
Case Is = Len(Left(VPCode, (InStr(1, VPCode, "-", vbTextCompare) - 1))) = BusDivNum
Case Else
BusDivNum = 4
End Select
Business_Division_Number = BusDivNum
End Function
ASKER
Hi Rey,
The AAA-B-END, BB-C-END, D-K-END were just 3 examples of many different entries in the field.
I need to utilize Select Case as there will be exceptions I need to build in (i.e. the first case below "B-E-End" should not return 1, but should return 2)
I tried to utilize your Instr code to modify my function, but now it is returning all null values. Maybe I am not using the Case select syntax correctly?
Option Compare Database
Function Business_Division_Number(V PCode As String)
Dim BusDivNum As Double
Select Case VPCode
Case Is = "B-E-END"
BusDivNum = 2
Case Else
BusDivNum = InStr(VPCode, "-") - 1
End Select
End Function
The AAA-B-END, BB-C-END, D-K-END were just 3 examples of many different entries in the field.
I need to utilize Select Case as there will be exceptions I need to build in (i.e. the first case below "B-E-End" should not return 1, but should return 2)
I tried to utilize your Instr code to modify my function, but now it is returning all null values. Maybe I am not using the Case select syntax correctly?
Option Compare Database
Function Business_Division_Number(V
Dim BusDivNum As Double
Select Case VPCode
Case Is = "B-E-END"
BusDivNum = 2
Case Else
BusDivNum = InStr(VPCode, "-") - 1
End Select
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome thanks : )
AAA-B-END
BB-C-END
D-K-END
you can simply use
Instr("AAA-B-END","-")-1
3
Instr("BB-C-END","-")-1
2
Instr("D-K-END","-")-1
1
Function Business_Division_Number(V
Business_Division_Number = Instr(VPCode,"-")-1
End Function