Results 1 to 4 of 4
  1. #1
    dandoescode is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    85

    Substring Query

    I'm interested in searching a table with a string valued field for variable definition statements. The sub string would be "AS ..." I think this might be done effectively using a LIKE statement



    Code:
    SELECT [Raw Data].Field1, *
    FROM [Raw Data]
    WHERE ((([Raw Data].Field1) Like "* As *"));
    However this will return the entire string containing the sub string, I'm only interested in the variable type, which comes three charterers after the A in AS. I can't use the Mid function, at least not in a trivial way, since the length before the AS varies. Anybody got a good idea on how to handle this?

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I think this is the code you are looking for:
    Code:
    SELECT Mid([FIELD1],InStr([FIELD1],"As")+1,3) AS VarType
    FROM [Raw Data]
    WHERE [Raw Data].Field1 Like "*As*";

  3. #3
    dandoescode is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    85
    Thanks dude, One change and the code did exactly what I was hoping for, plus now I know about the InStr function
    Code:
    SELECT Mid([FIELD1],InStr([FIELD1],"As")+2) AS VarType
    FROM [Raw Data]
    WHERE [Raw Data].Field1 Like "*As*";

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I'm only interested in the variable type, which comes three charterers after the A in AS
    I thought it was a little odd when you asked for what you said above, which sounds like you want the three characters after the "A", meaning starting with the "S" (instead of starting after the "S")...

    Anyway, glad it works for you!

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

Similar Threads

  1. Replies: 3
    Last Post: 03-14-2012, 06:48 AM
  2. IDCard Scanner substring
    By torpid in forum Programming
    Replies: 3
    Last Post: 05-30-2011, 07:16 AM
  3. Filtering using a Substring Help
    By r_walk76 in forum Forms
    Replies: 2
    Last Post: 01-11-2006, 07:13 AM

Tags for this Thread

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