Error: Extra ) in query expression

[BrittKnee]

I am receiving the below error when creating a form where my users can search for their clients. I keep getting the below error. I have run the query in design view and it runs without any error. Any help is appreciated!

SELECT Client_Data_qry.ID, Client_Data_qry.Client_Name, Client_Data_qry.Business_Type, Client_Data_qry.Contact_Person, Client_Data_qry.Contact_Nbr, Client_Data_qry.Contact_Email_Add, Client_Data_qry.Client_Name AS Client_Data_qry.Business_Type AS [Business Type] FROM Client_Data_qry WHERE (((Client_Data_qry.Client_Name) Like "*" & Forms!frmAdvancedSearch_Client_Data!txtSearch2 & "*")) Or (((Client_Data_qry.Business_Type) Like "*" & Forms!frmAdvancedSearch_Client_Data!txtSearch2 & "*")) Or (((Client_Data_qry.Contact_Person) Like "*" & Forms!frmAdvancedSearch_Client_Data!txtSearch2 & "*")) Or (((Client_Data_qry.Contact_Nbr) Like "*" & Forms!frmAdvancedSearch_Client_Data!txtSearch2 & "*")) Or (((Client_Data_qry.Contact_Email_Add) Like "*" & Forms!frmAdvancedSearch_Client_Data!txtSearch2 & "*")) ORDER BY Client_Data_qry.ID DESC;

 

[Joe4]

What exactly are you trying to accomplish with this?
What exactly is being added in all your Search box on the Form?

 

[BrittKnee]

I have created the below form.

In the rows I would like the search to return:


ID

Client_Name
Business_Type
Contact_Person
Contact_Nbr
Contact_Email_Add

Thanks!

 

[Joe4]

So, is your query supposed to search EVERY single field, or just the Client Name field?
Can you tell us the specific value you are entering in when you get this error?

 

[Norie]

BrittKnee

You don't really need any of the parentheses you have in the WHERE clause of the query.

SELECT Client_Data_qry.ID, Client_Data_qry.Client_Name, Client_Data_qry.Business_Type, Client_Data_qry.Contact_Person, Client_Data_qry.Contact_Nbr, Client_Data_qry.Contact_Email_Add, Client_Data_qry.Client_Name AS Client_Data_qry.Business_Type AS [Business Type]
FROM Client_Data_qry
WHERE Client_Data_qry.Client_Name Like "*" & Forms!frmAdvancedSearch_Client_Data!txtSearch2 & "*"
       OR Client_Data_qry.Business_Type Like "*" & Forms!frmAdvancedSearch_Client_Data!txtSearch2 & "*"
       OR Client_Data_qry.Contact_Person Like "*" & Forms!frmAdvancedSearch_Client_Data!txtSearch2 & "*"
       OR Client_Data_qry.Contact_Nbr Like "*" & Forms!frmAdvancedSearch_Client_Data!txtSearch2 & "*"
       OR Client_Data_qry.Contact_Email_Add Like "*" & Forms!frmAdvancedSearch_Client_Data!txtSearch2 & "*"
ORDER BY Client_Data_qry.ID DESC;

 

[JonXL]

I notice ([Client_Name])) isn't in the query you posted.

Can we see Client_Data_qry?

 

[jackd]

I have removed unnecessary "Client_Data_qry." and formatted your sql in post#5

SELECT ID
    ,Client_Name
    ,Business_Type
    ,Contact_Person
    ,Contact_Nbr
    ,Contact_Email_Add
    ,Client_Name AS Business_Type AS [Business Type] '<<<<<<<<<SYNTAX??????
FROM Client_Data_qry
WHERE Client_Name LIKE "*" & Forms ! frmAdvancedSearch_Client_Data ! txtSearch2 & "*"
    OR Business_Type LIKE "*" & Forms ! frmAdvancedSearch_Client_Data ! txtSearch2 & "*"
    OR Contact_Person LIKE "*" & Forms ! frmAdvancedSearch_Client_Data ! txtSearch2 & "*"
    OR Contact_Nbr LIKE "*" & Forms ! frmAdvancedSearch_Client_Data ! txtSearch2 & "*"
    OR Contact_Email_Add LIKE "*" & Forms ! frmAdvancedSearch_Client_Data ! txtSearch2 & "*"
ORDER BY ID DESC;

Check the line I have noted with <<<SYNTAX Seems Client name was supposed to be aliased?????

Try this one: (But you have Business_Type identified twice in your sql)

SELECT ID
    ,Client_Name
    ,[COLOR=rgb(44, 130, 201)]Business_Type[/COLOR]
    ,Contact_Person
    ,Contact_Nbr
    ,Contact_Email_Add
    ,Client_Name
    ,[COLOR=rgb(61, 142, 185)]Business_Type [/COLOR]AS [Business Type]
FROM Client_Data_qry
WHERE Client_Name LIKE "*" & Forms ! frmAdvancedSearch_Client_Data ! txtSearch2 & "*"
    OR Business_Type LIKE "*" & Forms ! frmAdvancedSearch_Client_Data ! txtSearch2 & "*"
    OR Contact_Person LIKE "*" & Forms ! frmAdvancedSearch_Client_Data ! txtSearch2 & "*"
    OR Contact_Nbr LIKE "*" & Forms ! frmAdvancedSearch_Client_Data ! txtSearch2 & "*"
    OR Contact_Email_Add LIKE "*" & Forms ! frmAdvancedSearch_Client_Data ! txtSearch2 & "*"
ORDER BY ID DESC;

 

[welshgasman]

Sticks out a bit?