Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The other answers point out the most important thing, which is that <code>InStr</code> actually returns the numeric <em>position</em> of one string in another (or 0 if the desired string isn't found). As they say, you should be testing the <em>condition</em> <code>&lt;result&gt; &gt; 0</code> in your <code>If</code> statement. I'll just address what the reason is behind your observation that your test "doesn't work (<strong>all the time</strong>)". It's a nice chance to revel in some ancient I-&lt;3-BASIC awesomeness.</p> <p>What's going on is that, <em>in this case</em> (see edit at the bottom for more) VBA's <code>And</code> operator (and <code>Or</code>, etc.) is actually a <em>bitwise</em> operator, not a <em>logical</em> one. That is, if you pass it two integer operands, it will do a bit-by-bit <code>And</code>, and return back the resulting integer. For example <code>42 And 99</code> evaluates to <code>34</code>, because (in binary) <code>0101010 And 1100011</code> is <code>0100010</code>.</p> <p>Now, normally, if you use VBA <code>Boolean</code> values, <code>And</code> works like a logical operator. This is because in VBA, the constant <code>True</code> is equal to the numeric <code>-1</code>, and <code>False</code> is equal to the numeric zero. Because VBA represents <code>-1</code> as a binary number with all bits set, and zero as a binary number with all bits cleared, you can see that binary operations become equivalent to logical operations. <code>-1 And &lt;something&gt;</code> always equals the same <code>&lt;something&gt;</code>. But if you're just passing any old numbers to <code>And</code>, you'll be getting back a number, and it won't always be a numeric value that is equal to the constants <code>True</code> or <code>False</code>.</p> <p>Consider a simple example (typed in the Immediate window):</p> <pre><code>x="abc" ?Instr(x,"a") 1 ?Instr(x,"b") 2 ?Instr(x,"c") 3 ?(Instr(x,"a") and Instr(x, "b")) 0 ?(Instr(x,"a") and Instr(x, "c")) 1 </code></pre> <p>Now recall that VBA's <code>If</code> statement treats any non-zero numeric argument as being the same as <code>True</code>, and a zero numeric argument as being the same as <code>False</code>. When you put all this together, you'll find that a statement of your example form:</p> <pre><code>IF INSTR(STR_TEXT,"/10'") AND INSTR(STR_TEXT,"/20'") THEN </code></pre> <p>will sometimes pick the first condition and sometimes the second, depending on just what is in the searched string. That's because sometimes the bitwise <code>And</code> operation will return zero and sometimes it will return non-zero. The exact result will depend on the exact positions of the found strings, and this clearly isn't what you'd expect. So <em>that's</em> why the advice you've already gotten matters in the details.</p> <p>EDIT: As pointed out by Hugh Allen in this comment:</p> <p><a href="https://stackoverflow.com/questions/7015471/does-the-vba-and-operator-evaluate-the-second-argument-when-the-first-is-false?noredirect=1#comment36622398_7015471">Does the VBA &quot;And&quot; operator evaluate the second argument when the first is false?</a></p> <p>VBA's <code>And</code> operator does actually return <code>Boolean</code> values of both of it's operands are <code>Boolean</code>. So saying that it's a bitwise operator is not strictly correct. It's correct for this problem though. Also, the fact that it <em>can</em> act as a bitwise operator does mean that it can't act like a "normal", purely logical, operator. For example, because it must evaluate both operands in order to determine if they are numbers or not, it can't short-circuit.</p>
    singulars
    1. This table or related slice is empty.
    1. POInstr condition doesn't work with 2 or more conditions
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USjtolle
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COVoted up I learned something too!
      singulars
      1. PO
      1. USxQbert
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload