Results 1 to 6 of 6
  1. #1
    caniread is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    86

    Function to cut off the end of a string after seeing "MIL"

    Hello everyone,



    I have a field that has a string that states different products. How would I write a code to get the products string stopping after it reads "MIL"? Example below.

    EG EDM 4MIL, 5" X 1000', WHITE

    would equal

    EG EDM 4MIL

    Thanks for the help

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    easy enough if you can use Left function, but that depends on info you have not revealed; i.e.
    - the pointer/position is always "MIL"?
    - pointer is always a comma (,)?
    last case would be easiest I think - combination of Left and Instr functions
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    caniread is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    86
    It is unfortunately different. Position can always be different and comma isn't always there. So it needs to look for "MIL" calculate its position then trim using that position.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Try
    Left(yourString,Instr(yourString,"MIL")+3)

    I gave you a clue in my first answer though and I think you will learn more by researching and trying vs us just providing the answer.

  5. #5
    caniread is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    86
    Your response did get me going in the right direction. Code below worked.

    Code:
    Public Function getMil(ByVal pvVal)Dim vRet
    Dim Leng As Integer
    Dim Prod As String
    
    
    Leng = InStr([pvVal], "MIL")
    Prod = Left(pvVal, Leng + 2)
    
    
    getMil = Prod
    
    
    End Function

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Nice to see you solved it. I had to get off of here in a hurry as momma was standing at the door with the dog. One of them was very anxious to go for their walk.

    I'd advise to code for the possibility that pvVal is Null. So you can do that and simplify a bit. I think it's not absolutely necessary to define the return property type for a function, but it certainly helps in 2 ways: Shows what is expected if troubleshooting 2) prevents other types, thus aids in error handling. I also note that you have declared a variable that you don't use. So something like
    Code:
    Public Function getMil(ByVal pvVal) As Variant
    
    If Len(pvVal & "") > 0 Then
      getMil = Left(pvVal,Instr(pvVal,"MIL")+2)
    Else
      'whatever function should return if there's no pvVal value
    End If
    
    End Function
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 08-27-2019, 07:25 PM
  2. Replies: 5
    Last Post: 04-22-2019, 08:55 PM
  3. Suppress "Requery" and/or "Repaint" when running function
    By GraeagleBill in forum Programming
    Replies: 3
    Last Post: 12-08-2017, 12:04 AM
  4. Replies: 3
    Last Post: 04-20-2016, 02:50 PM
  5. Replies: 2
    Last Post: 11-04-2011, 02:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums