+ Reply to Thread
Results 1 to 25 of 25

query SQL: search in database reccord that contains specific letter

  1. #1
    Forum Contributor
    Join Date
    05-07-2013
    Location
    France
    MS-Off Ver
    Excel 2007
    Posts
    122

    query SQL: search in database reccord that contains specific letter

    Hello!
    I have a database for medical devices on access! A search userform allows you to select the field you want to search and enter the value of the field you want to search!

    With this code I am able to seach for the device but I have to write the entire value correctly to search for the device
    Please Login or Register  to view this content.

    I want for example, if I want to search for all neuromonitor, to write just "neuro" in the texbox and the query shows me all reccords in the column that contain "neuro"
    I'm trying this but it's not working!
    Please Login or Register  to view this content.
    Any idea?

    Thank you for your help!

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620
    Sebastien

    The wildcards are kind of wrong.

    Try this.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    05-07-2013
    Location
    France
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: query SQL: search in database reccord that contains specific letter

    Thank you for your answer! I tried this code it's not working!

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: query SQL: search in database reccord that contains specific letter

    How does it not work?

  5. #5
    Forum Contributor
    Join Date
    05-07-2013
    Location
    France
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: query SQL: search in database reccord that contains specific letter

    Quote Originally Posted by Norie View Post
    How does it not work?
    If the query finds nothing that matchs in the database it shows a messagebox and with this code it shows me the messagebox so it finds nothing!!

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: query SQL: search in database reccord that contains specific letter

    How are you checking if nothing has been found?

  7. #7
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,213

    Re: query SQL: search in database reccord that contains specific letter

    have you tried using * instead of % depending on the way you're connecting to the database, it could be either

  8. #8
    Forum Contributor
    Join Date
    05-07-2013
    Location
    France
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: query SQL: search in database reccord that contains specific letter

    And i'm sure in my search that i have the device in the database!

    i check if nothing is found with:
    Please Login or Register  to view this content.

  9. #9
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,213

    Re: query SQL: search in database reccord that contains specific letter

    I doubt that line of code does what you think it does, you should be checking the recordset's EOF property:
    Please Login or Register  to view this content.

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: query SQL: search in database reccord that contains specific letter

    Sebastien

    Can you post the SQL statement?

    You can print it out to the immediate window with this.
    Please Login or Register  to view this content.
    Also, you could test the SQL in the database, change the %s to *s if you want to do that.

    Kyle

    I originally thought the problem might be the wildcard but I checked and for ADO you need to use %, if you use the * wildcard nothing is returned, well unless there's an exact match.

  11. #11
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,213

    Re: query SQL: search in database reccord that contains specific letter

    @Norie Yes, that's true, but could I thought it could be DAO - so thought it might be worth checking

  12. #12
    Forum Contributor
    Join Date
    05-07-2013
    Location
    France
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: query SQL: search in database reccord that contains specific letter

    This is my code if it can helps you!

    Please Login or Register  to view this content.

  13. #13
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: query SQL: search in database reccord that contains specific letter

    Kyle

    You're right, just assumed it was ADO because I was using that for something.

  14. #14
    Forum Contributor
    Join Date
    05-07-2013
    Location
    France
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: query SQL: search in database reccord that contains specific letter

    Even if I write the whole word for example Neuromonitor (And I have many Neuromonitors in the database) the messagebox appear to tell me nothing is found!
    Last edited by sebastienkanj; 06-11-2013 at 06:48 AM.

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: query SQL: search in database reccord that contains specific letter

    Try this.
    Please Login or Register  to view this content.

  16. #16
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,213

    Re: query SQL: search in database reccord that contains specific letter

    Drop the square brackets, it's searching for them:

    Please Login or Register  to view this content.

  17. #17
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: query SQL: search in database reccord that contains specific letter

    Sebastien

    I've noticed that for some reason you still have square brackets in the criteria term, they shouldn't be there, the code I posted removed them from the first code you posted.

    That could be the reason no records are returned, pretty sure there would be any values with [ or ] in the field.

    So try removing those brackets.
    Please Login or Register  to view this content.

  18. #18
    Forum Contributor
    Join Date
    05-07-2013
    Location
    France
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: query SQL: search in database reccord that contains specific letter

    Quote Originally Posted by Norie View Post
    Try this.
    Please Login or Register  to view this content.
    With this code I have an error on this line :
    Please Login or Register  to view this content.
    It says: non valid argument

  19. #19
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,213

    Re: query SQL: search in database reccord that contains specific letter

    Sorry Norie, I'll keep my neb out

  20. #20
    Forum Contributor
    Join Date
    05-07-2013
    Location
    France
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: query SQL: search in database reccord that contains specific letter

    Quote Originally Posted by Norie View Post
    Sebastien

    I've noticed that for some reason you still have square brackets in the criteria term, they shouldn't be there, the code I posted removed them from the first code you posted.

    That could be the reason no records are returned, pretty sure there would be any values with [ or ] in the field.

    So try removing those brackets.
    Please Login or Register  to view this content.
    You're right! It works with your code!! Thanks! I'll retry on several search and see if it works well!!

  21. #21
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: query SQL: search in database reccord that contains specific letter

    Kyle

    No problem.

  22. #22
    Forum Contributor
    Join Date
    05-07-2013
    Location
    France
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: query SQL: search in database reccord that contains specific letter

    Everything works well on my laptop (32 bits version) but when I copied the files on the company's computer (64 bits) there was an error message telling me that I need to update the code to 64 bits version and the following paragraph was in red color:

    Please Login or Register  to view this content.
    Do anyone know how to update the code? Im trying to read some article about compatibility between 32 bits and 64 bits..

  23. #23
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: query SQL: search in database reccord that contains specific letter

    Sebastien

    Is that code connected to the code in this post?

    If it isn't perhaps it might be an idea to start a new thread, save us getting confused (well me anyway).

  24. #24
    Forum Contributor
    Join Date
    05-07-2013
    Location
    France
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: query SQL: search in database reccord that contains specific letter

    Quote Originally Posted by Norie View Post
    Sebastien

    Is that code anything to do with the code in this post?

    If it isn't perhaps it might be an idea to start a new thread.
    Yes you're right, I'll mark this one as solved and start another one!

  25. #25
    Forum Contributor
    Join Date
    05-07-2013
    Location
    France
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: query SQL: search in database reccord that contains specific letter

    Quote Originally Posted by Norie View Post
    Sebastien

    Is that code anything to do with the code in this post?

    If it isn't perhaps it might be an idea to start a new thread.
    Yes you're right, I'll mark this one as solved and start another one!

+ 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