alteredd — 2011-08-29T12:09:49-04:00 — #1
A co-worker of mine is working on a database query to pull from a memo field. He wants it only to show tables with the word vi in it. The problem he is running into is it's giving him words like VIsit and inVIsible. How can he write the query to only return the exact value just VI.
Some of the VI words in the body contain quotes "VI" some are " VI" with spaces etc.
Any help would be appreciated, thanks!
r937 — 2011-09-04T17:33:15-04:00 — #2
those instr examples do not cover the situations where VI begins the value, or VI ends the value, or VI is itself surrounded by quotes...
verschha — 2011-09-04T09:50:48-04:00 — #3
OR simply use:
WHERE InStr(1, [fieldname], " VI.", 0) > 0 OR InStr(1, [fieldname], " VI ", 0) > 0
InStr Function - Access - Office.com
r937 — 2011-08-29T12:11:44-04:00 — #4
alteredd — 2011-08-29T12:35:39-04:00 — #5
r937 — 2011-08-29T12:46:33-04:00 — #6
well, it's going to be messy...
WHERE field LIKE '* vi *'
OR field LIKE '* vi'
OR field LIKE 'vi *'
OR field LIKE '*"vi"*'
OR field LIKE '*" vi"*'
OR field LIKE '*"vi "*'
and keep adding patterns as you discover them