Results 1 to 4 of 4
  1. #1
    mainerain is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Location
    Maine, USA
    Posts
    146

    vba to extract four numbers from a string

    So the example would be TT-0007A

    I know how to extract the 0007 but then I think I want to convert it to an integer so I can then do some logic that says if this four digit number is equal to or greater than 0200 but equal to or less than 299 THEN....


    (by the way what would be the Syntax for equal to or greater than? equal to or less than?)

    The Val function would ignore the 000 and give just 7 I believe, which doesn't work for me.

    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You cannot convert it to numeric else it becomes a 7, but strings can still do <>=.

    SNum=left(right([field],5),4)
    If sNum>="0200" and <="0299" then

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You say you know how to extract 0007 and you want to convert it to an integer but then 7 doesn't not work for you. Confusing......

    Code:
    Dim iExtract as integer
    
    iExtract =mid([YourFieldHere],4,4)
    
    
    If iExtract <=299 then
    ........
    End If
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    mainerain is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Location
    Maine, USA
    Posts
    146
    Thanks for the responses. Helpful.

    This is what worked for me...
    Code:
                strDash = InStr(1, strTag, "-") ' find the position of the FIRST Dash
                strContents = Mid(strTag, strDash + 1) ' extract contents to the RIGHT of the Dash
                intExtractLoopNum = Val(strContents) ' extract the numbers while ignoring any 0 that proceeds a number larger than 0
                ExtractLoopNum '  run subroutine that sets strDepart
    Last edited by mainerain; 04-17-2020 at 01:51 PM.

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

Similar Threads

  1. Extract date from a string
    By stalk in forum Queries
    Replies: 3
    Last Post: 04-10-2020, 08:35 AM
  2. Replies: 2
    Last Post: 10-21-2015, 12:00 PM
  3. function to extract numbers from string
    By Ruegen in forum Programming
    Replies: 4
    Last Post: 04-12-2015, 07:59 PM
  4. Extract a number from a string
    By webisti in forum Access
    Replies: 3
    Last Post: 09-16-2013, 08:29 AM
  5. Extract numbers from text string strored in a field.
    By khabdullah in forum Programming
    Replies: 2
    Last Post: 12-23-2007, 06:55 PM

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