Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 759
  • Last Modified:

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(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
           
        Case Else
            BusDivNum = 4
        End Select
       
    Business_Division_Number = BusDivNum

End Function
0
grmcra
Asked:
grmcra
  • 2
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
is this "AAA-B-END, BB-C-END, D-K-END" the content of the textbox or

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(VPCode As String)

   
    Business_Division_Number = Instr(VPCode,"-")-1
End Function
0
 
grmcraAuthor Commented:
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(VPCode 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
0
 
Rey Obrero (Capricorn1)Commented:
test this


Function Business_Division_Number(VPCode As String)

Dim BusDivNum As integer
      Select Case VPCode
       
        Case Is = "B-E-END"
            BusDivNum = 2
       
        Case Else
            BusDivNum = InStr(VPCode, "-") - 1
           
        End Select
Business_Division_Number=BusDivNum
End Function
0
 
grmcraAuthor Commented:
Awesome thanks : )
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now