Solved

Access 2010 Function - problem with InStr function

Posted on 2014-04-30
4
742 Views
Last Modified: 2014-04-30
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
Comment
Question by:grmcra
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40032765
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
 

Author Comment

by:grmcra
ID: 40032832
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40032934
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
 

Author Closing Comment

by:grmcra
ID: 40032978
Awesome thanks : )
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question