Results 1 to 4 of 4
  1. #1
    robbeh is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    55

    Question Like query not returning all results

    I have a database with over 80 000 records in a table with about 15 fields. I am creating a form so the user can easily query the table without having to create custom queries.

    The query form looks something like:
    First Name: [TEXTBOX]
    Last Name: [TEXT BOX]
    Billing Number: [TEXTBOX]
    etc.
    [SEARCH BUTTON]


    The [SEARCH BUTTON] triggers the query that looks at the main table and each field of that table using a the following Like statement:

    (Under the first name filed)Criteria: Like [Forms]![frmFullSearch]![txtFirstName] & "*"
    (Under the lastname field)Cirteria: Like [Forms]![frmFullSearch]![txtLastName] & "*"

    The issue that I just noticed is, it doesn't pull all the results. For example I might put "Rob" in the FirstName text box and get 4 results with first name "Robert"....however, if I remove the Like statement and just use an exact look up ([Forms]![frmFullSearch]![txtFirstName]) and type "Robert" I get 50 results.

    Am I missing something with the Like statement? Should I be setting up my query in a different way? Basically the goal is to allow the user to query a database using multiple text fields on a form and only having to enter partial entries (example "Rob" should also include "Robert" "Robbeh" etc).

    This has been killing me for hours.

    Robb

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Is it because of the other boxes? Are you NOT searching them if they are null?
    Like:

    Code:
    if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
    if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
    if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value
        'remove 1st And
    sWhere= mid(sWhere,4)
      'just use the filter
    me.filter = sWhere
    me.filterOn = true
       'OR   
       'apply the sql to the form
    sSql = "SELECT * FROM tblCompany WHERE " &  sWhere

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The dynamic parameterized query should work, however, I don't use them. I use code like ranman shows to apply filter to form or report.

    DoCmd.OpenForm "form name", , , sWhere

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    robbeh is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    55
    Thanks for the responses. I would love to but there is extremely sensitive data. If it comes to a last resort I will definitely consider this option.

    Basically every field criteria in the query looks like "Like [Forms]!..." calling the appropriate text field. Most of the time only one of the 10 txtFields is being entered when I the query. Is that where my problem lies? Is there a why without using code to simply tell the query not to run that criteria IF Null?

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

Similar Threads

  1. Query Parameters Returning All Results
    By turk1559 in forum Queries
    Replies: 5
    Last Post: 07-07-2014, 08:02 AM
  2. Query not returning all expected results
    By amenitytrust in forum Queries
    Replies: 6
    Last Post: 11-05-2012, 07:13 AM
  3. Query returning more results than wanted
    By thedanch in forum Queries
    Replies: 4
    Last Post: 06-19-2012, 08:24 AM
  4. Replies: 5
    Last Post: 10-27-2011, 09:08 PM
  5. Query not returning all of the results
    By velvettiger in forum Queries
    Replies: 4
    Last Post: 03-11-2010, 06:56 AM

Tags for this Thread

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