Results 1 to 7 of 7
  1. #1
    aligahk06 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2010
    Posts
    94

    Query to match only first three character and pull all records corresponding to the field.

    Dear All,
    The following is the query.
    SELECT TenentList.[Tenent's Name], TenentList.[Unit No], TenentList.Status, TenentList.[Authorization Letter], TenentList.[Open Date], TenentList.[Close Date], TenentList.[Authorization Letter], TenentList.[Authorization Letter].FileData, TenentList.[Authorization Letter].FileName, TenentList.[Authorization Letter].FileType, TenentList.Remarks
    FROM TenentList
    WHERE (((TenentList.[Tenent's Name])=[Enter the tenent's name]));



    I want to enter the three possible characters for tenent's name then it pull all records that contains first three character.

    Please modify my query in where clause so that i can pull the record.
    Note : criteria will be in query design view.

    Thanks..

    Rgds,
    aligahk06

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Code:
    SELECT TenentList.[Tenent's Name], TenentList.[Unit No], TenentList.Status, TenentList.[Authorization Letter], TenentList.[Open Date], TenentList.[Close Date], TenentList.[Authorization Letter], TenentList.[Authorization Letter].FileData, TenentList.[Authorization Letter].FileName, TenentList.[Authorization Letter].FileType, TenentList.Remarks
    FROM TenentList
    WHERE ((Left(TenentList.[Tenent's Name],3)=Left([Enter the tenent's name],3)));

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    If avril's suggestion does not do what you require provide some example data and the outcome required

  4. #4
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    or:
    Code:
    ....
    WHERE  TenentList.[Tenent's Name] like [Enter the tenet's name] & "*"
    This gives the possibility to enter as many start chars as you want.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Isn't this more or less the same question?

    https://www.accessforums.net/showthr...900#post457900
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    MBasha is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Sydney
    Posts
    1
    WHERE ((Left(TenentList.[Tenent's Name],3) like Left([Enter the tenent's name],3) & '*'));

  7. #7
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Tip: try not to make a calculation in the first part of your criterion expression. It prevents the use of indices and on top of that it needs to be executed for every record in the data set, slowing down your query considerably

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

Similar Threads

  1. Replies: 9
    Last Post: 08-19-2013, 01:08 PM
  2. Replies: 3
    Last Post: 04-24-2013, 08:29 AM
  3. Replies: 3
    Last Post: 02-06-2013, 10:00 PM
  4. Replies: 2
    Last Post: 02-22-2012, 02:36 AM
  5. Replies: 12
    Last Post: 10-15-2010, 10:03 AM

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