Results 1 to 4 of 4
  1. #1
    paosav is offline Novice
    Windows Vista Access 2003
    Join Date
    Apr 2010
    Posts
    2

    Use of "%" dummy in connection with "like" function

    I can't find the way for a correct use of "%"
    in connection with WHERE and LIKE.

    My current code is

    SELECT t.Campo1 INTO Extracted
    FROM tablebase AS t, SearchedList AS s
    WHERE (((t.Campo1) Like [s].[Cerca]));



    My table to be searched, (t) is

    Red bloom
    Blue ribbon
    Green Leaf
    Yellow umbrella
    Red table
    Violet dream
    Orange blossom
    Green tea
    Indigo virgin
    Black sabbath

    and the table of items to be found (s), is

    Red
    Green

    ..........

    with the code I quoted I only get exact match,
    so result is null. how do I place the % char here, for partial match?

  2. #2
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Here is a example how you can do a partial serach using Like. My ZipCodes re string and in the Where condition I have put Like [TypeName]+"*" No if I type 9 it will show me all the ZipCodes starting with 9.

    SELECT [Table1].[ID], [Table1].[PName], [Table1].[ZipCode]
    FROM Table1
    WHERE ((([Table1].[ZipCode]) Like [TypeName]+"*"));

    In your code Try adding:

    WHERE (((t.Campo1) Like [s].[Cerca]+"*"));

    if this solves your problem mark this thread solved.

  3. #3
    paosav is offline Novice
    Windows Vista Access 2003
    Join Date
    Apr 2010
    Posts
    2
    Thanks, Maximus, seems to work fine,
    but just to complete the theme,
    would you be so kind to tell me
    how the % char can take to the same result in Access,
    as per title of my post,
    so that I come to know alternative ways to come to a solution?

  4. #4
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    char % doesnot go with like in access unlike SQL. The query wild-card characters are different in DAO than in ADO. so use '*' instead of '%' while working in access. for more information follow this link for more information.

    http://support.microsoft.com/kb/225048

    To learn how to use like operator in access:

    http://support.microsoft.com/kb/294954

    Please mark this thread solved as I understand your problem is solved and you know how to deal with such problem.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access 2003 Date() Function returns "#Name?"
    By smartel@soprema.ca in forum Programming
    Replies: 5
    Last Post: 01-18-2012, 05:52 AM
  2. Replies: 4
    Last Post: 02-11-2010, 02:21 AM
  3. aSTR = Dir("C:\*.*") >> "Type Mismatch"
    By JGrant in forum Programming
    Replies: 1
    Last Post: 03-28-2009, 05:17 AM
  4. Replies: 2
    Last Post: 08-31-2006, 12:19 PM
  5. "Count" and "Countif" in Reports
    By JMantei in forum Reports
    Replies: 1
    Last Post: 06-20-2006, 02:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums