Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2008
    Posts
    27

    Unanswered: Searching using a Query

    Hi there
    I have a database used for song titles on CDs.

    I am using a query to search for the song, and in the Criteria field i have entered:
    [Enter song name]
    However - the song name has to be EXACT to show any results. For example, when searching for the song "I Will Survive" it must be written as:

    I Will Survive

    If just "Will Survive" is typed in, nothing is returned.

    Is there any way of changing the search in the query to be slightly more general?

    Many thanks for your help in advance

    adam

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,928

    DVD Example

    Here's an example of something I created for cataloging DVDs. There might be a few things you can utilize for your database. It's got a nice alphabetical type search as well as entering parts of the title (enter text in the yellow text box below the word Like in the upper right.)
    Attached Files Attached Files
    Last edited by pkstormy; 10-19-08 at 19:24.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    To directly answer the question, use the following criteria:

    Like "*" & [Enter some text to find in the song name:] & "*"
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,444
    Provided Answers: 12
    Psst, ST...

    You're missing some quotage
    Code:
    LIKE "'*" & [Enter some text to find in the song name:] & "*'"
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you may want to consider breaking multiple words into individual search terms, but that means writing your own SQL on the fly

    eg
    <- user types in "I Will Survive"
    where title like "%WILL%" AND title like "%SURVIVE%" will return any songtitles which contain will and survive. so that would include any word that is or contains the specified words (eg WILLing, WILLiam etc..)

    where title like "%WILL%" OR title like "%SURVIVE%" will return any songtitles with either will or survive in

    Code:
    where title like "WILL %" or title like "% WILL" or title like "% WILL %" or title = "WILL"
    will return any songtitles which have WILL in the title. the reason for the 4 terms is to cater for the different ways in which the specified word is found

    "WILL %" means anything starting with WILL, followed by a space, followed by another letter eg "WILL survive" but not WILL not survive
    "% WILL" means there must be at least space before before the specified word
    "% WILL %" means there must be a space either side of the word
    "WILL" checks for ONLY that word, eg it will find titles = "WILL".

    so for I will Survive
    Code:
    where (title like "I %" or title like "% I" or title like "% I %" or title = "I")
    AND (title like "WILL %" or title like "% WILL" or title like "% WILL %" or title = "WILL")
    AND (title like "SURVIVE %" or title like "% SURVIVE" or title like "% SURVIVE %" or title = "SURVIVE")
    you can break down the users words using the split function, then create your SQL based on the number of words found
    I don't reccommend that you search for I.. some db engines put a lower limit on at the number of characters you can match for
    Last edited by healdem; 10-20-08 at 04:22.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by georgev
    Psst, ST...

    You're missing some quotage
    D'OH!! I'll try not to do it again boss
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Aug 2008
    Posts
    27
    hi guys

    thanks for all your help!

    with this mode:

    Like "*" & [Enter some text to find in the song name:] & "*"

    is there a problem when searching for a song that includes punctuation?
    for example the song: "Can't Get You Out Of My Head" if you just search for "Cant get you" it doesn't find anything.


    Healdem - would you mind pointing me in the right direction for the split function in the SQL statement?

    Thanks everyone so much.
    Adam

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can break down the users words using the split function, then create your SQL based on the number of words found
    you cannot use split in a SQL function directly

    I don't see how you could use it in a user function in SQL

    as suggested before I'd expect to use that sort of construct in code to create the SQL dynamically in code and then execute it
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,928
    I use syntax like that below which handles an apostrophe in the answer:

    LIKE """ & [Enter some text to find in the song name:] & """"
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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