Results 1 to 8 of 8
  1. #1
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    328

    Filter field based on ohter field content

    I am using Access 2007 and 2003. I would like a query to detect if one field contains certain
    sequential letters from another field

    Field1:------------- Field2:
    Express 27---- --- Alsberg Express 27
    Santa Cruz 27-----Bill Lee Santa Cruz 27
    Santana 22 -------Merit 25
    etc.



    The query would then take, say the left 3 letters of field1 (Exp, San etc), and filter the query
    for Field2 entries that contained those 3 letters in the same sequence. Possible? Any help
    much appreciated.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe
    Code:
    SELECT Table1.ID, Left([Field1],3) AS Exp1, Table1.Field2
    FROM Table1
    WHERE (((Table1.Field2) Like "*" & Left([Field1],3) & "*"));
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  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
    An alternative would be to use the InStr() function: Instr([Field2],Left([Field1],3))>0

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    328
    Thanks much-It works great. Get -1 if contains and 0 if not.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    328
    Thanks much. I decided to use the other reply as a way to modify my query. I will work with this later. I assume that I have to create a form to use it?

  7. #7
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    328
    I don't know if my other reply went to you or to Gicu. Confusing. I am using Gicu's reply as I can apply it directly to the query.. I will try this code later. Assume that I have to create a module.

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    If using the InStr suggestion just add True (or -1) in the Criteria row to make the query return only the records that contain the string you're after, no module needed. You can uncheck the Show check-box if you don't need the extra field as part of your query results.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 17
    Last Post: 09-27-2019, 03:33 AM
  2. Replies: 1
    Last Post: 12-04-2016, 05:43 PM
  3. Replies: 1
    Last Post: 04-30-2015, 08:33 PM
  4. User Restrictions based on Field Content in MS Access
    By gori1084 in forum Database Design
    Replies: 7
    Last Post: 05-29-2014, 12:18 PM
  5. Field content based on date calculation
    By jlmnjem in forum Programming
    Replies: 6
    Last Post: 09-23-2010, 10:24 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