Results 1 to 7 of 7
  1. #1
    sisva18 is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2021
    Posts
    7

    Multiple likes used as user input

    Hey I have this problem with my query.
    I want to filter it based on user input.
    When I only add one like as the item no.
    The search is correct. But how would I be able to like other criteas such as a item name or batch no.
    This seems to alter my data to something which is not correct.



    So here I use the Like to like the tfFilter in my form. I also want the user to be able to use other values than the ITEMID. How can I do this to build upon the query without destroying the current data?


    Before I just used and or condition after the first like to like the second field and so on but it did not work-

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Don't know if this will help or not
    http://allenbrowne.com/ser-62.html

    Perhaps you'd rather have dynamic sql? Then I think you still need a form, and some event on the form (button click?) will gather the form data and build a sql statement. For example to use something other than ITEMID you could get the applicable field from a combo list.

    Your post reads as if you think the LIKE operator has something to do with "liking" a post that someone makes. If that's the case, then that's the wrong interpretation. It's simply a type of comparison that allows you to find results where the criteria is similar to what you provide.

    One way to solve your issue might be to create the query using actual values that could come from your form (e.g. "22" for ID) and the LIKE operator and appropriate wild cards. When that works as expected, substitute the literal values for form control references (e.g. Forms!myFormName.myControlName).

    See https://support.microsoft.com/en-us/...0-242cde582e0b
    for the wildcards you can use.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    sisva18 is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2021
    Posts
    7
    Quote Originally Posted by Micron View Post
    Don't know if this will help or not
    http://allenbrowne.com/ser-62.html

    Perhaps you'd rather have dynamic sql? Then I think you still need a form, and some event on the form (button click?) will gather the form data and build a sql statement. For example to use something other than ITEMID you could get the applicable field from a combo list.

    Your post reads as if you think the LIKE operator has something to do with "liking" a post that someone makes. If that's the case, then that's the wrong interpretation. It's simply a type of comparison that allows you to find results where the criteria is similar to what you provide.

    One way to solve your issue might be to create the query using actual values that could come from your form (e.g. "22" for ID) and the LIKE operator and appropriate wild cards. When that works as expected, substitute the literal values for form control references (e.g. Forms!myFormName.myControlName).

    See https://support.microsoft.com/en-us/...0-242cde582e0b
    for the wildcards you can use.
    The thing is I have a input textbox where the user can write the itemnumber, name of item or a alias. I thought maybe to make a query for each individual scenario and check it in vba to substitute the query accordingly? But that seems like a hassel...

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you can post some sql examples so we can see what 3 or 4 would look like that would help a lot. You may need extra queries for some scenarios, but if you're doing something like this for example
    SELECT * FROM myTable WHERE myTable.ID = 6
    SELECT * FROM myTable WHERE myTable.ID = 7
    then definitely only one query.

    If it's more like SELECT * FROM myTable WHERE myTable.ID = x AND EmployeeID = y

    then still only one query. If the requirement is very dynamic, then it's often best IMO to build your sql entirely in code and do something with that. None of that is very complicated if you know how and that's where the forum can help. We just need to know the requirements, and the more detail the better.

    An input box is not a good method for providing query criteria because the user can put anything in. Listboxes, comboboxes and other form controls are much "safer" and practical.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    sisva18 is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2021
    Posts
    7
    Found a solution thank you for the help

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Well please post it, as it might help others, in the future?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    According to the forum email I got there was something else you wanted to know. I take it that you figured that out.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-27-2020, 03:51 PM
  2. Replies: 1
    Last Post: 02-16-2018, 03:38 AM
  3. Query with user input and sort by multiple fields
    By ultrarunner2017 in forum Access
    Replies: 5
    Last Post: 12-23-2017, 07:51 PM
  4. Replies: 1
    Last Post: 10-12-2017, 06:12 PM
  5. Replies: 1
    Last Post: 05-29-2017, 05:21 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