Results 1 to 5 of 5
  1. #1
    Join Date
    May 2012
    Posts
    24

    Unanswered: Query Search for text string - Syntax Help

    Hi,

    I'm trying to make an error checker within my database. Each record can have anywhere between 0 and 42 errors (all combinations). So I have a separate form where the user picks the type they are looking for, and doing so populates a locked Textbox [training needs]. The user then clicks the button which launches a query. The intention is- search through the database to find all results containing that error.

    I've tried the following query however this works however it only returns EXACT results. i.e. If i pick "Clarity" it will only show results where Clarity was the only error - it will not show results where there were Clarity + Anything else:

    SELECT [FTP Marking Sheet_Q].[Date of Recorded Call], [FTP Marking Sheet_Q].Advisor, [FTP Marking Sheet_Q].Score, [FTP Marking Sheet_Q].[Training Needs], [FTP Marking Sheet_Q].[Order Result], [FTP Marking Sheet_Q].[Add-On Result], [FTP Marking Sheet_Q].[Customer Experience Result]
    FROM [FTP Marking Sheet_Q]
    WHERE ((([FTP Marking Sheet_Q].[Training Needs]) Like [Forms]![ErrorFinder]![Training Needs]));


    Just as a test I tried to manually type in the string into the query and doing this gave me the full desired results Clarity + Anything Else:

    SELECT [FTP Marking Sheet_Q].[Date of Recorded Call], [FTP Marking Sheet_Q].Advisor, [FTP Marking Sheet_Q].Score, [FTP Marking Sheet_Q].[Training Needs], [FTP Marking Sheet_Q].[Order Result], [FTP Marking Sheet_Q].[Add-On Result], [FTP Marking Sheet_Q].[Customer Experience Result]
    FROM [FTP Marking Sheet_Q]
    WHERE ((([FTP Marking Sheet_Q].[Training Needs]) Like "*Clarity,*"));

    - i can't use this however because i need it to be more dynamic

    So my question is, how can I amend this line so that it show's the full results:
    WHERE ((([FTP Marking Sheet_Q].[Training Needs]) Like [Forms]![ErrorFinder]![Training Needs]));

    I've already tried:
    Like "*[Forms]![ErrorFinder]![Training Needs]*"
    but that actually shows nothing at all, so i'm obviously doing something wrong

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    "WHERE [FTP Marking Sheet_Q].[Training Needs]) Like '*" &  [Forms]![ErrorFinder]![Training Needs] & "*'"
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If you are searching for a text value, that value must be delimited (so the sql engine knows where the value starts and stops.

    Hence Sinndho's suggestion.

    Numeric literals dont need delimiters, but dates do (use the iso date format yyyy-mm-dd delimited by the hash symbol #
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    May 2012
    Posts
    24
    Thank you both for your help!

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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