Help with SQL code

[Engenhus]

I need a littlle help adapting this piece of code to my purposes:

SELECT Clientes.[Codigo Cliente LAE], Clientes.Nome, Clientes.Endereço, Clientes.Cidade, Clientes.[Codigo Postal], Clientes.Telefone, Clientes.Fax, Clientes.[Numero Contribuinte]
FROM Clientes
GROUP BY Clientes.[Codigo Cliente LAE], Clientes.Nome, Clientes.Endereço, Clientes.Cidade, Clientes.[Codigo Postal], Clientes.Telefone, Clientes.Fax, Clientes.[Numero Contribuinte]
HAVING (((Clientes.[Codigo Cliente LAE]) Like "*" & Forms![Pesquisa de Clientes]!PCTextPesq2 & "*"));

I need to replace "Codigo Cliente LAE" by "Forms![Pesquisa de Clientes]!PCComboPesq" (I need to keep the [] of the the original code, just replace "Codigo Cliente LAE"). I've tried several ways but still get a sintax error.

Thanks in advance.

[Moniker]

There are two ways to do it, one automated and a little more complex, and one less automated but easier (depending on your skill level).

The easier way is to make what you need to replace a parameter. Then you get prompted for it each time you run the query. It's not exactly automatic -- you have to enter the information into that box each time -- but it's very simple. (If you're not sure how to make a parameter value, search this forum.)

The fully automatic way is to place the SQL into a table and then reference that table in code using a QueryDefs. This isn't as hard as it sounds.

1. Make a new table named "t_SQL". Place two fields in it, one called "SQL_Name" as Text, and the other called "SQL" as a Memo.
2. Add one new record to t_SQL. In SQL_Name, put whatever the name of the query you're trying to run is. In SQL, copy/paste in your query. We'll make a few minor changes to that shortly.
3. Assuming you are already using a form command button to invoke the query, you'll need to put a little extra code behind the button.
4. Add this code to your module or behind the button on the form (and don't forget to add DIM strSQL As String to the top of the command button's subroutine):

    strSQL = DLookup("SQL", "t_SQL", "SQL_Name='YOURQUERYNAMEHERE'")
    strSQL = Replace(strSQL, "{CLIENTES}", Forms![Pesquisa de Clientes]!PCComboPesq)
    CurrentDb.QueryDefs("YOURQUERYNAMEHERE").SQL = strSQL

In the above, replace YOURQUERYNAMEHERE with the query name, the same name as the actual query and as the query name you placed in t_SQL in the SQL_Name field.

Finally, make these change to your SQL (from your post) in the t_SQL table:

SELECT Clientes.[{CLIENTES}], Clientes.Nome, Clientes.Endereço, Clientes.Cidade, Clientes.[Codigo Postal], Clientes.Telefone, Clientes.Fax, Clientes.[Numero Contribuinte]
FROM Clientes
GROUP BY Clientes.[{CLIENTES}], Clientes.Nome, Clientes.Endereço, Clientes.Cidade, Clientes.[Codigo Postal], Clientes.Telefone, Clientes.Fax, Clientes.[Numero Contribuinte]
HAVING (((Clientes.[{CLIENTES}]) Like "*" & Forms![Pesquisa de Clientes]!PCTextPesq2 & "*"));

What all of this is doing is making your SQL dynamic. Each time you click on the command button that opens the query, the code goes to the table t_SQL, locates the SQL you want (based on the query's name), makes the appropriate changes to the SQL, and then rewrites the actual query. In query view, this will look just like a normal query. It's simply being rewritten from the SQL in t_SQL on each run.

Hope that helps.

~Moniker

[Engenhus]

Thanks! I tried the second way and it worked great!