Like not working in access 2007

[skmaddur]

SELECT Icd10cm_order_2016.OrderNumber, Icd10cm_order_2016.Icd10CM, Icd10cm_order_2016.ShortDesc, Icd10cm_order_2016.LongDesc
FROM Icd10cm_order_2016
WHERE (((Icd10cm_order_2016.OrderNumber)=[Forms]![SearchICD10CM]![OrderNumber]) AND
((Icd10cm_order_2016.Icd10CM) Like "*" & ([Forms]![SearchICD10CM]![Icd10CM]) & "*") AND
((Icd10cm_order_2016.ShortDesc) Like "*" & ([Forms]![SearchICD10CM]![ShortDesc]) & "*") AND
((Icd10cm_order_2016.LongDesc) Like "*" & ([Forms]![SearchICD10CM]![LongDesc]) & "*"));


I cannot find anything wrong with this code above, returns "no results", for field2(text), field3(text), field4(text). field1 (number field) is returning the matching row.

 

[Ranman256]

i think you want ORs, not ANDs

WHERE (((Icd10cm_order_2016.OrderNumber)=[Forms]![SearchICD10CM]![OrderNumber]) OR
((Icd10cm_order_2016.Icd10CM) Like "*" & ([Forms]![SearchICD10CM]![Icd10CM]) & "*") OR
((Icd10cm_order_2016.ShortDesc) Like "*" & ([Forms]![SearchICD10CM]![ShortDesc]) & "*") OR
((Icd10cm_order_2016.LongDesc) Like "*" & ([Forms]![SearchICD10CM]![LongDesc]) & "*"));

 

[skmaddur]

Thanks for the response.
Just to clarify;

An example;

Field1 = 34 ;
returns the row 34, with the values of 4 columns (number, icd, short desc, long desc)
Field 2 = A0;
does not return the rows from the table that match this text.

 

[Ranman256]

i dont think you want to search in all fields, esp if nothing is in them.

 

[skmaddur]

When all blank fields, I want it to return all the rows in the table.

 

[skmaddur]

Logic error.
it works fine, if I change the field1 search to also be Like function.

Thanks ranman, in helping out with my 1st succesffull ms access 2007 program.

Basically, my code now looks like the following;

SELECT Icd10cm_order_2016.OrderNumber, Icd10cm_order_2016.Icd10CM, Icd10cm_order_2016.ShortDesc, Icd10cm_order_2016.LongDesc
FROM Icd10cm_order_2016
WHERE (((Icd10cm_order_2016.OrderNumber) Like "*" & ([Forms]![SearchICD10CM]![OrderNumber]) & "*") AND
((Icd10cm_order_2016.Icd10CM) Like "*" & ([Forms]![SearchICD10CM]![Icd10CM]) & "*") AND
((Icd10cm_order_2016.ShortDesc) Like "*" & ([Forms]![SearchICD10CM]![ShortDesc]) & "*") AND
((Icd10cm_order_2016.LongDesc) Like "*" & ([Forms]![SearchICD10CM]![LongDesc]) & "*"));

 

[ss8880]

try this
Alike "%"&[srchtxt]& "%"
ALike "%" & ([Forms]![SearchICD10CM]![Icd10CM]) & "%")
:banghead: