Search Query Problem - Weird Results.

[DarkOne72]

Hello,
I have a search query to a single table and then goes to a form. My problem is that I use the Like "*" & [Forms]![csearch]![STEERING_PILOT] & "*" and it works great but when I add my other criterias so I can have 4 different search areas it returns either no results or randomized different set of records not even pertaining to the search.
Its like once I add two or more columns to search it acts weird. I am including the SQL code and also the LIKE commands I used for different columns.

Like "*" & [Forms]![csearch]![STEERING_PILOT] & "*"

Like "*" & [Forms]![csearch]![PILOT_NAME] & "*"

Like "*" & [Forms]![csearch]![INBOUND_SWITCH_TERMINATION] & "*"

Like "*" & [Forms]![csearch]![INBOUND_DIALED_NUMBER] & "*"

these commands once put in the criteria puts out the following sql in design mode (all spaces are correct and in this one I even tried putting it in 2 slots of criterias):

SELECT [Call Flows].STEERING_PILOT, [Call Flows].INBOUND_DIALED_NUMBER, [Call Flows].INBOUND_SWITCH_TERMINATION, [Call Flows].PILOT_NAME, [Call Flows].OUTBOUND_DIALED_NUMBER, [Call Flows].NCG, [Call Flows].CG1, [Call Flows].CG2, [Call Flows].CG4, [Call Flows].[Mon-Fri], [Call Flows].[Sat-Sun], [Call Flows].[Mon-Sat], [Call Flows].[Mon-Sun], [Call Flows].Sat, [Call Flows].Sun
FROM [Call Flows] INNER JOIN ACDC ON [Call Flows].ID = ACDC.ID
WHERE ((([Call Flows].STEERING_PILOT) Like "*" & [Forms]![csearch]![STEERING_PILOT] & "*") AND (([Call Flows].INBOUND_DIALED_NUMBER) Like "*" & [Forms]![csearch]![INBOUND_DIALED_NUMBER] & "*") AND (([Call Flows].INBOUND_SWITCH_TERMINATION) Like "*" & [Forms]![csearch]![INBOUND_SWITCH_TERMINATION] & "*") AND (([Call Flows].PILOT_NAME) Like "*" & [Forms]![csearch]![PILOT_NAME] & "*")) OR ((([Call Flows].STEERING_PILOT) Like "*" & [Forms]![csearch]![STEERING_PILOT] & "*") AND (([Call Flows].INBOUND_DIALED_NUMBER) Like "*" & [Forms]![csearch]![INBOUND_DIALED_NUMBER] & "*") AND (([Call Flows].INBOUND_SWITCH_TERMINATION) Like "*" & [Forms]![csearch]![INBOUND_SWITCH_TERMINATION] & "*") AND (([Call Flows].PILOT_NAME) Like "*" & [Forms]![csearch]![PILOT_NAME] & "*"));

Can someone please help

 

[Golom]

First ... you have duplicated your LIKE clauses. Your selection criteria boil down to

WHERE
     (
     [COLOR=blue]a group of LIKE clauses[/color]
     )
  OR
     (
     [COLOR=red]the same group of LIKE clauses[/color]
     );

Second ... the arguments for LIKE need to be in single quotes. for example

Like "*" & [Forms]![csearch]![STEERING_PILOT] & "*"

should be

Like "[COLOR=red yellow]'*" & [Forms]![csearch]![STEERING_PILOT] & "*[COLOR=red yellow]'[/color]"[/color]

Third ... The single quote character has special meaning in the use of LIKE as do the "[" and "]" characters. You may need to take some action to deal with those characters if the users will (or may) type those into the text boxes.

 

[DarkOne72]

Thank you for the quick response! I have tried it both with single criteria and doubles. Also, I have tried the single quotes and I am getting the same results. Here is the updated SQL:

SELECT [Call Flows].STEERING_PILOT, [Call Flows].INBOUND_DIALED_NUMBER, [Call Flows].INBOUND_SWITCH_TERMINATION, [Call Flows].PILOT_NAME, [Call Flows].OUTBOUND_DIALED_NUMBER, [Call Flows].NCG, [Call Flows].CG1, [Call Flows].CG2, [Call Flows].CG4, [Call Flows].[Mon-Fri], [Call Flows].[Sat-Sun], [Call Flows].[Mon-Sat], [Call Flows].[Mon-Sun], [Call Flows].Sat, [Call Flows].Sun
FROM [Call Flows]
WHERE ((([Call Flows].STEERING_PILOT) Like '*' & [Forms]![csearch]![STEERING_PILOT] & '*') AND (([Call Flows].INBOUND_DIALED_NUMBER) Like '*' & [Forms]![csearch]![INBOUND_DIALED_NUMBER] & '*') AND (([Call Flows].INBOUND_SWITCH_TERMINATION) Like '*' & [Forms]![csearch]![INBOUND_SWITCH_TERMINATION] & '*') AND (([Call Flows].PILOT_NAME) Like '*' & [Forms]![csearch]![PILOT_NAME] & '*'));

Thanks for your help.

 

[DarkOne72]

UPDATE!
I have kept the Like command as you said and also had to duplicate them on the criteria lines again but 3 of the 4 are working. I can not get the STEERING_PILOT one to work at all, it just give blank records. If I take out the other search criterias and just have the steering pilot it works fine. WEIRD....

Thanks Again