How do I run a general SQL LIKE statement with specific exclusions?
<p>I am writing a query in MS Access¹ towards an Oracle database, where I am trying to find everyone incarcerated in prisons in Norway during the census of 1875. To do this, I have to search for keywords in several different fields, as the ennumerators would describe their state several different ways, e.g.:</p>

<ul>
<li>By describing their family connection as ‘Fange’, ‘Arrestant’ (prisoner) or the likes.</li>
<li>By listing them as temporarily present at a location named ‘Fængsel’ (prison) or the likes.</li>
<li>If at their family’s home, by listing them as absent and naming their assumed location as above.</li>
<li>By listing them as living in a separate building, named ‘Fængsel’ or the likes.</li>
<li>By listing their profession as ‘Fange’, ‘Arrestant’ (<em>v.s.</em>) or the likes.</li>
</ul>

<p>One of the keywords I am looking for, is variants of ‘Bodsfængsel’ (a ‘prison of penitence’). Problems is, ‘Bod’ can also mean <em>stall/small building</em>. To make sure I include all variants of ‘Bodsfængsel’, I have written the following lines (only the relevant code snippet is shown here; a complete section is shown below):</p>

<pre><code>Or BOSTNVN Like "*bod*"
    And
    (
        BOSTNVN not Like "*bode*" or
        BOSTNVN not Like "*dbod*" or
        BOSTNVN not Like "*bodg*"
    )
</code></pre>

<p>Problem is, when I run this code, I get 8513 hits, exactly the same number of lines as when I exclude the <code>not like</code>-lines.</p>

<p>Comments:
The exclusions are to avoid hits on placenames such as ‘Tol<strong>dbod</strong>’, ‘<strong>Bode</strong>n’ or ‘Told<strong>bodg</strong>ade’
If I somehow could get it to output extra fields (e.g. Field0, Field1, Field2 et c.) to show where the hit was found, it would probably make it much easier to find the errors too.
I hope I have provided all necessary information. Please don’t chop my head off!</p>

<p>¹ Access 2007 on Win 7 Enterprise.</p>

<p>Here is a complete snippet from the beginning of the query; if this is not necessary, please advise, and I will remove it:</p>

<pre><code>SELECT
    KOMMNR, KRETSNR, BOSTNR, PERSNR,
    ⋮
PID  
FROM
    FOLKETELLINGER_PERSON_1875  
WHERE
    (
        (
            KOMMNR Not Like "11*" 
            And KOMMNR Not Like "12*" 
            ⋮
            And KOMMNR Not Like "17*"
        ) 
        AND (
            SEDVBO Like "*Fæng*" 
            Or SEDVBO Like "*fæng*" 
            ⋮
            Or SEDVBO Like "*arest*"
            And
            (
                SEDVBO not Like "*Bode*" or
                SEDVBO not Like "*dBod*" or
                SEDVBO not Like "*Bodg*"
            )
            Or SEDVBO Like "*bod*"
            And
            (
                SEDVBO not Like "*bode*" or
                SEDVBO not Like "*dbod*" or
                SEDVBO not Like "*bodg*"
            )
            Or SEDVBO Like "*Bot*"
            And
            (
                SEDVBO not Like "*Bote*" or
                SEDVBO not Like "*dBot*" or
                SEDVBO not Like "*Botg*"
            )
            Or SEDVBO Like "*bot*"
            And
            (
                SEDVBO not Like "*bote*" or
                SEDVBO not Like "*dbot*" or
                SEDVBO not Like "*botg*"
            )
        )
    ) 
    OR (
    ⋮
    ); 
</code></pre>