Results 1 to 6 of 6

Substring in VBA Access

  1. #1
    newbie30 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    7

    Substring in VBA Access

    Hi,



    I have the below value in a field,

    57x40 mm MACHINE TILL CREDIT CARD,PDQ THERMAL PAPER ROLLS CASH REGISTER RECEIPT[2 x Box 20 ( 40 rolls )]


    I need to find the number '2' after the [ and accordingly do some looping.

    I have tried to use
    Dim idx As Integer
    idx = InStr(Me.ItemTitle, "[")

    If idx > 0 Then
    title = MID(Me.ItemTitle, 1, idx - 1)
    End If

    Could someone provide a better solution pl.

    Thanks

    I

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    5,022
    try

    myval=iif(instr(mystring,"[")<>0, val(mid(mystring,instr(mystring,"[")+1)),0)

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    11,718
    Sub testBra()
    Dim s As String, x As Integer
    s = "57x40 mm MACHINE TILL CREDIT CARD,PDQ THERMAL PAPER ROLLS CASH REGISTER RECEIPT[2 x Box 20 ( 40 rolls )]"
    x = InStr(s, "[")
    If x > 0 Then
    Debug.Print Mid(s, x + 1, 1) ''to get first char after the "["
    End If
    End Sub

    OOOOps: I see ajax has already posted a solution

  4. #4
    newbie30 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    7
    Thank you.that worked like a charm

  5. #5
    newbie30 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    7
    Yep Ajax solution worked perfectly..Thank you Orange for your reply too..

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    11,718
    ajax and I are happy to help.

    You asked a specific question and had1 instance of your data. If you have other formats, you may want to look for a more general solution, or identify patterns and have a routine to address each pattern.
    Good luck.

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

Similar Threads

  1. getting substring of a column value
    By funandlearning in forum Access
    Replies: 3
    Last Post: 06-19-2012, 08:49 AM
  2. Substring Query
    By dandoescode in forum Queries
    Replies: 3
    Last Post: 06-15-2012, 11:54 AM
  3. Replies: 3
    Last Post: 03-14-2012, 05:48 AM
  4. IDCard Scanner substring
    By torpid in forum Programming
    Replies: 3
    Last Post: 05-30-2011, 06:16 AM
  5. Filtering using a Substring Help
    By r_walk76 in forum Forms
    Replies: 2
    Last Post: 01-11-2006, 06:13 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
  •  
Tech Forums: Microsoft Office Forums