SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: Access Query

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    May 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Access Query

    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!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    microsoft access?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    May 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes ms access

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    well, it's going to be messy...
    Code:
    WHERE field LIKE '* vi *'
       OR field LIKE '* vi'
       OR field LIKE 'vi *'
       OR field LIKE '*"vi"*'
       OR field LIKE '*" vi"*'
       OR field LIKE '*"vi "*'
       OR ...
    and keep adding patterns as you discover them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Jan 2007
    Location
    Almere, The Netherlands
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    well, it's going to be messy
    OR simply use:

    Code SQL:
    WHERE InStr(1, [fieldname], " VI.", 0) > 0 OR InStr(1, [fieldname], " VI ", 0) > 0

    InStr Function - Access - Office.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •