Access 2010 Function - problem with InStr function
Posted on 2014-04-30
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(VPCode 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
BusDivNum = 4
Business_Division_Number = BusDivNum