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

[ironfelix717]

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,

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

[June7]

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.

[accesstos]

Add the line of 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!

[ironfelix717]

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"

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

LIKE '*b*'

should be

LIKE "*b*"

Can you verify this syntax is correct?

[ironfelix717]

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.

[pbaldy]

If you're using the string variable for an ADO recordset, you need % as the wildcard.

[orange]

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;"

[pbaldy]

Didn't realize the thread was closed, now opened again.