SQL doesn't run in vba

[myrt]

Hi,
I have a Sql code that works. I need to implement this code with a filter based in a variable. When I tried to import the sql in vba, I couldn't make it run. Could you help, please?

SELECT CLIENTI.IDCliente, CLIENTI.CodCliente, CLIENTI.NomeCliente, CLIENTI.RagioneSociale, CLIENTI.SettoreClienteID, CLIENTI.ClienteAttivo, CLIENTI.DataInizioRapporto, CLIENTI.Città, CLIENTI.Telefono
FROM CLIENTI
WHERE (((CLIENTI.CodCliente) Like "*" & forms!Risultati!SearchText & "*")) Or (((CLIENTI.NomeCliente) Like "*" & forms!Risultati!SearchText & "*"));

In vba I tried these measures:
to trasform " in chr(34) since it's used to enclode the code
however it doesn't work as it should

Dim SQL As String

SQL = "SELECT CLIENTI.IDCliente, CLIENTI.CodCliente, CLIENTI.NomeCliente, " & _
      "CLIENTI.RagioneSociale, CLIENTI.SettoreClienteID, CLIENTI.ClienteAttivo, " & _
      "CLIENTI.DataInizioRapporto, CLIENTI.Città, CLIENTI.Telefono " & _
      "FROM CLIENTI " & _
      "WHERE (((CLIENTI.CodCliente) Like & chr(34) & chr(42) & chr(34) & [forms]![Risultati]![SearchText] & chr(34) & chr(42) & chr(34)))"
      
Me.SearchResults.RowSource = SQL

 

[CJ_London]

try

"WHERE CLIENTI.CodCliente Like '*" & forms!Risultati!SearchText & "*' Or CLIENTI.NomeCliente Like '*" & forms!Risultati!SearchText & "'"

 

[myrt]

Thanks for your fast reply! I tried but it didn't work.
Just as with the use of chr(34) it doesn't give an error anymore, however it doesn't work correctly either (and by that I mean that it doesn't work at all, I have blanc results before even typing a letter).
the sql code is used to make a search filter.
So the "*" are intended to make possible a midword search in different fields. (the code is made following a tutorial here on the site)

 

[myrt]

Works! thanks to everyone for your advice

WHERE ((CLIENTI.CodCliente) Like '*' & Forms!Risultati!SearchText & '*' )

 

[Cronk]

What do you mean that the second criteria does not filter? The WHERE clause will give records that satisfy either criteria. If you want both results where both parts are true, then use AND instead of OR.

Incidentally, the single quotes inside the sql string should be replaced with chr(34) if your search text contains a single quote.

 

[myrt]

Guys! Need your help, please.
I'm trying to refer to a checkbox in this sql code, however I can't manage to get it right.
It 's something of the sort:

"AND CLIENTI.ClienteAttivo = False " & _

How can I make Access read that 'false' value? Thanks in advance

 

[myrt]

Found: must be Yes/No and carefully considered the logic of Or and And.

 

[myrt]

Found: must be Yes/No and carefully considered the logic of Or and And.
If someone needs a reference, this may be of help:

https://support.office.com/en-us/ar...criteria-08029ed5-39f1-4900-88e3-ab473b16d94e

 

[Cronk]

If your WHERE part of your sql depends on the value of a check box on your form, use
"AND CLIENTI.ClienteAttivo =" & me.chkYourCheckBox
or
"AND CLIENTI.ClienteAttivo = NOT " & me.chkYourCheckBox