Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


INSTR Used as Query Criteria

Posted on 2005-04-29
Medium Priority
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 66

Accepted Solution

Jim Horn earned 1000 total points
ID: 13894938
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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 total points
ID: 13894996
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


Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

579 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