Results 1 to 6 of 6
  1. #1
    wrkadri is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2012
    Posts
    29

    Question Search Query not giving all results


    Hi all,
    I have been working on upgrading my database. I created a search query where I used a form to search for certain data. In this query, I used the criteria " Like "*" & [forms]![Search]![PUAMI Reference] & "*"
    When I search, I get results but few stays missing. For example, let's say I have 200 people living in Detroit, but when I search for Detroit, I get 100 results.
    If you can help me, I need to find the whole 200 records to be shown.

    Thank you in advance.

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Suggest you post your SQL statement for starters so it can be analyzed. Difficult to say what your issue is without seeing data and at least your query.

  3. #3
    wrkadri is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2012
    Posts
    29
    The search query is for one table (main), here's the SQL statement:

    SELECT main.[PUAMI Reference], main.Canceled, main.[Reason for Cancelation], main.[Name of PUAMI assessor], main.[Assessment Date], main.[Month/year of Arrival], main.[GPS Coordinates], main.[Village ID], main.[Village-Area], main.[Name of Head of HH], main.[Tel No of Head of HH], main.[Family Book No], main.[Existing Situation -Economic Status ID], main.[Monthly rent USD], main.[HCR Registration No], main.[Total no of displaced family members], main.[Area of Origin], main.[Total rooms occupied by displaced family], main.[People per room], main.Nationality, main.[ID Number], main.[Existing Situation -Physical Status ID], main.[Name of House Owner], main.[Lebanese ID No], main.[Tel No of Owner], main.[Total rooms in House], main.[No of persons between 18 and 55], main.[Working people (18-55)], main.[Total Family Income Per Month]

    WHERE (((main.[PUAMI Reference]) Like "*" & [forms]![Search]![PUAMI Reference] & "*") AND ((main.[Name of PUAMI assessor]) Like "*" & [forms]![Search]![PU Assessor] & "*") AND ((main.[Assessment Date]) Like "*" & [forms]![Search]![Assessment Date] & "*") AND ((main.[Village-Area]) Like "*" & [forms]![Search]![Village-Area] & "*") AND ((main.[Name of Head of HH]) Like "*" & [forms]![Search]![Name of Head of HH] & "*") AND ((main.[Tel No of Head of HH]) Like "*" & [forms]![Search]![Tel No of Head of HH] & "*") AND ((main.[Family Book No]) Like "*" & [forms]![Search]![Family Book Number] & "*"));














    Click image for larger version. 

Name:	search query.png 
Views:	17 
Size:	65.1 KB 
ID:	10929

  4. #4
    wrkadri is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2012
    Posts
    29
    Any Clue ?

  5. #5
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Your WHERE clause is saying that all the conditions must be met. This is because you have them all on the same line of the query grid. Is this in fact what you are looking for? Your query looks normal and I don't see any issues.

  6. #6
    wrkadri is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2012
    Posts
    29
    Though it looks normal, but I have 3044 records in this table and if I keep the search fields empty and click on the runquery button, I should get the whole 3044 records. Yet, I get only 2062 records. On the other hand, when I search for specific records, I get blank results. I don't know what's the problem with this. Everything seems to be right.

    Thank you for your help, I'll try something else.

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

Similar Threads

  1. Calculation in Query giving wrong results
    By dargo72 in forum Queries
    Replies: 11
    Last Post: 11-07-2012, 05:39 AM
  2. Replies: 3
    Last Post: 07-13-2012, 09:18 AM
  3. DCount on a Query giving #Name? error
    By Huddle in forum Access
    Replies: 9
    Last Post: 06-20-2012, 11:40 AM
  4. Access giving wrong result using sql query
    By amlanroy in forum Access
    Replies: 3
    Last Post: 05-10-2012, 10:11 AM
  5. Replies: 4
    Last Post: 08-16-2011, 05:54 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