Results 1 to 8 of 8

MS Access VBA - Query with 'LIKE' clause STRING VARIABLE syntax

  1. #1
    ironfelix717 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    34

    Post MS Access VBA - Query with 'LIKE' clause STRING VARIABLE syntax

    Searching does nothing for this topic, which is incredible. Such a fundamental function.

    I am having trouble implementing a 'LIKE' wildcard clause for a textbox value in an SQL string.

    Below is some code i've attempted. In any case, when the syntax is correct, no results return, which is due to the LIKE clause itself determined through troubleshooting.

    Thanks for any help.
    Regards,



    Code:
    Sub ()
    
    Dim procid                          As Variant 
    Dim sqlSearchAll                 As String   
    Dim SQL                             As String
    procid = tbProcID.Text
    
    
    SQL = "SELECT MfgProcesses.ID FROM Vers_Process INNER JOIN MfgProcesses ON (Vers_Process.MaxOfVersion = MfgProcesses.Version) AND (Vers_Process.ID = MfgProcesses.ID) HAVING (((MfgProcesses.ID) LIKE '*" & procid & "*') ORDER BY MfgProcesses.ID;"
    
    
    'NOTE:  Vers_Process is a query named Vers_Process
    
    End sub


  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    45,843
    LIKE and wildcard is really only useful with a text type field. I suspect ID is a number type. If criteria is ID 2 then you will get every record with a 2: 2, 12, 21, 129, 372, etc. Is that what you want?

    If you want the value in a textbox, use Value property and since Value is default property, don't even have to reference it.

    This SQL should use WHERE instead of HAVING.

    Code builds SQL string but does nothing with it.

    The Sub declaration has no name.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  3. #3
    accesstos's Avatar
    accesstos is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    55
    Add the line of code
    Code:
    Debug.Print SQL
    after the SQL assignment, to see, in immediate window (Ctrl+G) of VBA, what SQL realy contains.

    Remember that you can read the .Text property of a control only if the control have the focus.
    Is a useful property for the OnChange event of controls, not in OnClick event of a button.

    But, i prefer to enable users to choose the desired criteria avoiding the wildcards in the code.
    With fixed wildcards in the code, I will never find only the first names that begins with "J", if I type J* in the search field of a form.
    The best solution is the usage of a combo box with all posibilities of search (Starts with, Ends with, Contains etc),
    providing the proper wildcard for each choice, compined with a search text box, to eliminate the need of wildcards in search text by the user.

    Good continuing!

  4. #4
    ironfelix717 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    34
    Hi,

    Thanks for the responses.

    To be clear, the code is reduced. Minor errors are insignificant. .Text is required for the change event. This is a live filtered search.

    The ID field contains short text data that is alphanumeric. (Ex: ANNO3)

    I would however like the results to be returned for "begins with" rather than double asterisk.

    Regardless, I need insight on how this SQL string should be constructed with the use of a code string (variant) variable.

    Here is SQL string printed when typing in the textbox "b"

    Code:
    SQL = "SELECT MfgProcesses.ID FROM Vers_Process INNER JOIN MfgProcesses ON (Vers_Process.MaxOfVersion = MfgProcesses.Version) AND (Vers_Process.ID = MfgProcesses.ID) WHERE (((MfgProcesses.ID) LIKE '*" & procid & "*') AND (((MfgProcesses.Active) = True)) ORDER BY MfgProcesses.ID;"
    
    
    
    debug.print:
    SELECT MfgProcesses.ID FROM Vers_Process INNER JOIN MfgProcesses ON (Vers_Process.MaxOfVersion = MfgProcesses.Version) AND (Vers_Process.ID = MfgProcesses.ID) WHERE (((MfgProcesses.ID) LIKE '*b*') AND (((MfgProcesses.Active) = True)) ORDER BY MfgProcesses.ID;
    I believe the
    Code:
    LIKE '*b*'
    should be
    Code:
    LIKE "*b*"
    Can you verify this syntax is correct?

  5. #5
    ironfelix717 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    34
    UPDATE:

    This is amusing lol. Literally nothing is working. If you generate a LIKE statement with MS Access query builder its literally the exact statement just with double quotes around the text... Which I just managed to do in my SQL string and it still return NOTHING for results. The rest of the SQL is correct. No question about that.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,331
    If you're using the string variable for an ADO recordset, you need % as the wildcard.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,337
    Try this search criteria with Google ms access examples of like operator
    to find a number of examples.

    For clarity

    where x Like "*ABC" ---------------------x ends with ABC
    where x Like "ABC*" ---------------------x starts with ABC
    where x Like "*ABC*" --------------------x contains ABC

    Your issue may be with quotes within a quoted string.

    I recommend you use " (double quote to enclose the entire string) and
    single quote ' to deliminate string variables/values with the string.

    Your string for MfgProcesses.ID Contains

    SQL = "SELECT MfgProcesses.ID FROM Vers_Process INNER JOIN MfgProcesses ON (Vers_Process.MaxOfVersion = MfgProcesses.Version) AND (Vers_Process.ID = MfgProcesses.ID) WHERE MfgProcesses.ID LIKE '*' & procid & '*' AND MfgProcesses.Active = True ORDER BY MfgProcesses.ID;"

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,331
    Didn't realize the thread was closed, now opened again.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 3
    Last Post: 05-28-2013, 11:53 AM
  2. MySQL Syntax Error from Query String in VBA
    By raynman1972 in forum Programming
    Replies: 2
    Last Post: 09-17-2012, 04:59 PM
  3. Replies: 1
    Last Post: 06-08-2012, 01:45 PM
  4. Incomplete Syntax Clause (syntax error)
    By ajetrumpet in forum Programming
    Replies: 4
    Last Post: 09-11-2010, 09:47 AM
  5. Where clause syntax
    By stupesek in forum Reports
    Replies: 7
    Last Post: 09-03-2010, 01:26 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
  •  
Tech Forums: Microsoft Office Forums