INSTR Used as Query Criteria

Posted on 2005-04-29
Last Modified: 2006-11-18
I read about a clever use of the Instr function along with a criteria to select subsets of records. An additional calculated field is added with the syntax = "InStr(1,[Enter Full WP Numbers  separated by commas,Blank=All],[tblOrigBudg].[WP])" (without quotes). In the criteria field you put ">0 Or Is Null" (without quotes).

When executed, you can enter the variable (wp in this instance), and it returns the recordset. It works most of the time, but I have a situation where when I ask for 'F1.13', I also get 'F1.1'.

How can I correct this?


Question by:rdperkins
    LVL 65

    Accepted Solution

    You can't.  InStr finds all instances, regardless of whether it is a 'whole word' in the string you're searching, or just part of it.
    LVL 119

    Assisted Solution

    by:Rey Obrero
    how are using it?
    from the data below with this query

    SELECT A.ID, InStr([Ver],"F1.13") AS Expr1
    FROM A

    ID            Ver
    1             F1.1
    2             F1.13
    3             F1.13.2
    4             F1.11

    you will get a result like this

    ID      Expr1
    1         0
    2         1
    3         1
    4         0


    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now