• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

INSTR Used as Query Criteria

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?


2 Solutions
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
Rey Obrero (Capricorn1)Commented:
how are using it?
from the data below with this query

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

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now