+ Reply to Thread
Results 1 to 19 of 19

Query searching returns incorrect results

  1. #1
    Registered User
    Join Date
    11-07-2009
    Location
    australia
    MS-Off Ver
    Excel 2003
    Posts
    79

    Query searching returns incorrect results

    After making some small chages to my search code I have found that my search is now returning incorrect results.

    What is suppposed to happen is that when I search for a value in the volume field the search is supposed to return all results that match the full or partial search term (using "like" criteria in my query)

    What is actually happening is that the search is only matching exact search criteria.
    Any partial search requests result in the msgbox stating no matching results found.


    This is the code that is found in the query for the volume field:
    Please Login or Register  to view this content.
    The above code is actually present on about 4 diff lines in the query tab.
    I could post a screenshot later if reqd.

  2. #2
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Version 2301 (Build 16012.20000) Win 11 Home 64 Bit
    Posts
    22,285

    Re: Query searching returns incorrect results

    Are you trying to get results that contain the criteria in any part of the volume? ie. you put in sugar in the search box and are looking for the results to be maple sugar syrup, sugar pops, candy sugars, etc. So sugar can be at the beginning, middle or end of the volume? If this is the case, then you need to add an "*" & right after Like, so that Access knows this. Right now it is only looking for volumes that begin with sugar (if you use my example)

    Alan
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    11-07-2009
    Location
    australia
    MS-Off Ver
    Excel 2003
    Posts
    79

    Re: Query searching returns incorrect results

    Yes the results returned are supposed to be any results that include the partial search criteria used in the search form.

    Will have a look at that and post back.

  4. #4
    Registered User
    Join Date
    11-07-2009
    Location
    australia
    MS-Off Ver
    Excel 2003
    Posts
    79

    Re: Query searching returns incorrect results

    Quote Originally Posted by alansidman View Post
    Are you trying to get results that contain the criteria in any part of the volume? ie. you put in sugar in the search box and are looking for the results to be maple sugar syrup, sugar pops, candy sugars, etc. So sugar can be at the beginning, middle or end of the volume? If this is the case, then you need to add an "*" & right after Like, so that Access knows this. Right now it is only looking for volumes that begin with sugar (if you use my example)

    Alan
    Tried what you suggested and it still doesn't seem to work like it's supposed to .
    This is what it looks like after I made the change:
    Please Login or Register  to view this content.
    The strange thing is - the query line for the title field search is exactly the same as the old line for the volume search ... and it works perfectly as expected.

    Please Login or Register  to view this content.
    and the old volume field line
    Please Login or Register  to view this content.
    I really don't understand why changing the data type on the volume field in the table would have caused this problem.

  5. #5
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Version 2301 (Build 16012.20000) Win 11 Home 64 Bit
    Posts
    22,285

    Re: Query searching returns incorrect results

    A couple of questions.
    1. What was your data type before the change
    2. what is your data type now
    3. are you using a combo box to make your selection
    4. if you are using a combo box, is the related lookup table linked to your query

    Alan

  6. #6
    Registered User
    Join Date
    11-07-2009
    Location
    australia
    MS-Off Ver
    Excel 2003
    Posts
    79

    Re: Query searching returns incorrect results

    1. The data type was numeric
    2. The data type is now text.
    3. No it's just a text box.
    4. irrelevant because i'm not using a combo box

  7. #7
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Version 2301 (Build 16012.20000) Win 11 Home 64 Bit
    Posts
    22,285

    Re: Query searching returns incorrect results

    I don't have a definitive answer so I am grasping for straws at this time. Try changing the second '!' in your Like criteria to a '.' The one between the form name and the field name. I know that they are sometimes interchangeable and really shouldn't make a difference, but sometimes Access does some strange things also.

    Alan

  8. #8
    Registered User
    Join Date
    11-07-2009
    Location
    australia
    MS-Off Ver
    Excel 2003
    Posts
    79

    Re: Query searching returns incorrect results

    ok will try it and get back to you

  9. #9
    Registered User
    Join Date
    11-07-2009
    Location
    australia
    MS-Off Ver
    Excel 2003
    Posts
    79

    Re: Query searching returns incorrect results

    Did you mean to change this:
    Please Login or Register  to view this content.
    to this:
    Please Login or Register  to view this content.
    I have done this but am now getting a runtime error that will not allow me to test it.

    Runtime error 2004: There isn't enough memory to perform the operation. Close unnecessary programs and try again

    The highlighted debug line is:
    Please Login or Register  to view this content.


    I did try and add more tables, fields and forms to the database but surely access would be able to cope with modification of the files in this fashion ?
    Last edited by vampyr07au; 05-28-2011 at 12:38 PM.

  10. #10
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Version 2301 (Build 16012.20000) Win 11 Home 64 Bit
    Posts
    22,285

    Re: Query searching returns incorrect results

    This ispart of the code behind your command button to run the search?


    The highlighted debug line is:
    Code:
    DoCmd.OpenForm "search results"
    Are you able to post the entire code for that command button?

    Also, are you running the query from the QBE or from a SQL Statement in VBA?

    If from the QBE, would you post the entire SQL statement for the Query? Having bits and pieces doesn't give us the whole story.

    Alan
    Last edited by alansidman; 05-29-2011 at 06:42 AM.

  11. #11
    Registered User
    Join Date
    11-07-2009
    Location
    australia
    MS-Off Ver
    Excel 2003
    Posts
    79

    Re: Query searching returns incorrect results

    Quote Originally Posted by alansidman View Post
    This ispart of the code behind your command button to run the search?



    Are you able to post the entire code for that command button?

    Also, are you running the query from the QBE or from a SQL Statement in VBA?

    If from the QBE, would you post the entire SQL statement for the Query? Having bits and pieces doesn't give us the whole story.

    Alan
    Here is the code for the command button
    Please Login or Register  to view this content.

    As to your other question about how the query is being called, I must profess to not actually knowing that.
    As far as I was aware the query is being run from within the above code or when the
    Please Login or Register  to view this content.
    line runs.

    The out of memory error is only occurring when an attempt to open that particular form and it doesnt matter if the attempt is made via the button or by trying to dbl click the form in the object list on the left hand side.

    Any other form opens perfectly well.

    I can provide a copy of the entire project if it would make things easier ?

  12. #12
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Query searching returns incorrect results

    Can you try storing the entire search phrase as a variable so you can check the variable before running the query? Something like this:

    Please Login or Register  to view this content.
    Then use
    Please Login or Register  to view this content.
    and the immediate window to find what you are actually searching with.

    Also be aware that you query needs to be updated to search for text. Since you were using numbers before, your query would have been something like:

    Please Login or Register  to view this content.
    but now it needs to be like this:

    Please Login or Register  to view this content.
    where the variable is surrounded by single quotes.
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave

  13. #13
    Registered User
    Join Date
    11-07-2009
    Location
    australia
    MS-Off Ver
    Excel 2003
    Posts
    79

    Re: Query searching returns incorrect results

    Quote Originally Posted by davegugg View Post
    Can you try storing the entire search phrase as a variable so you can check the variable before running the query? Something like this:

    Please Login or Register  to view this content.
    Then use
    Please Login or Register  to view this content.
    and the immediate window to find what you are actually searching with.

    In relation to the above comment - I'm sorry but I do not understand what you mean by
    and the immediate window to find what you are actually searching with.
    In relation to the below comment - wrapping it in the single quotes makes sense since that is what was reqd in the code found in the cmd button that controls the search however I do not understand where
    SELECT * FROM tbl WHERE field1
    came from?
    Also be aware that you query needs to be updated to search for text. Since you were using numbers before, your query would have been something like:

    Please Login or Register  to view this content.
    but now it needs to be like this:

    Please Login or Register  to view this content.
    where the variable is surrounded by single quotes.
    The query is simply this:
    Please Login or Register  to view this content.
    Last edited by vampyr07au; 05-31-2011 at 10:18 PM.

  14. #14
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Query searching returns incorrect results

    I just put a generic sql statement in.
    Please Login or Register  to view this content.
    is not a valid SQL statement, it is just part of a WHERE clause. What is you whole SQL statement?

  15. #15
    Registered User
    Join Date
    11-07-2009
    Location
    australia
    MS-Off Ver
    Excel 2003
    Posts
    79

    Re: Query searching returns incorrect results

    Quote Originally Posted by davegugg View Post
    I just put a generic sql statement in.
    Please Login or Register  to view this content.
    is not a valid SQL statement, it is just part of a WHERE clause. What is you whole SQL statement?
    I'm sorry i'm still a little lost - I'm very new to all of this and have been using a lot of stuff that was provided to me after asking for assistance.

    The line
    Please Login or Register  to view this content.
    is what is found in the query section of my database project in the column for the volume field.

    As previously stated I can provide a copy of the entire project if it will make things easier.

    I'm not sure where the sql statement would be ... there is the query and there is code that operates after clicking a search button, that's all I know.

  16. #16
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Query searching returns incorrect results

    When you go into the query editor, there should be a button on a toolbar that changes the query from design view to view to SQL view to datasheet view. It wil probably look like a data table, and it will have a small black arrow pointing down next to it. Press the black arrow, then click on the option that says SQL. This will bring up the text of the SQL query. Copy that and show us what it is.

  17. #17
    Registered User
    Join Date
    11-07-2009
    Location
    australia
    MS-Off Ver
    Excel 2003
    Posts
    79

    Re: Query searching returns incorrect results

    ok here ya go

    Please Login or Register  to view this content.
    That's the complete code via SQL view.

  18. #18
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Query searching returns incorrect results

    Hmm, nothing jumps out at me. Try this instead:

    Please Login or Register  to view this content.
    If that doesn't work, I'd try creating a new query now that your table structure has changed, and see if that gives you the results you are looking for.

  19. #19
    Registered User
    Join Date
    11-07-2009
    Location
    australia
    MS-Off Ver
    Excel 2003
    Posts
    79

    Re: Query searching returns incorrect results

    nope still no go .... a partial search is giving me the no results msg box still .... full search is returning the out of memory runtime error.

    I am going to have a play with it over the next few days and will post back later.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1