Query All Fields Using 2 Different Criteria

[dwichmann]

Hi, i have written a query that takes one criteria string and searches every field returning any records containing that string. I would like to add a second criteria and return any records containing both criteria. Copy of existing query below, please help

SELECT TblQuotation.[Quotation Number], TblQuotation.[Raised By], TblQuotation.Customer, TblQuotation.Contact, TblQuotation.[Quotation Description], TblQuotation.Location, TblQuotation.Product, TblQuotation.[Quotation Result], TblQuotation.[Quotation Status], TblQuotation.Requote, TblQuotation.[quotation value]
FROM TblQuotation
WHERE (((TblQuotation.[Quotation Number]) Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box & "*")) Or (((TblQuotation.[Raised By]) Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box & "*")) Or (((TblQuotation.Customer) Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box & "*")) Or (((TblQuotation.Contact) Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box & "*")) Or (((TblQuotation.[Quotation Description]) Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box & "*")) Or (((TblQuotation.Location) Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box & "*")) Or (((TblQuotation.Product) Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box & "*")) Or (((TblQuotation.[Quotation Result]) Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box & "*")) Or (((TblQuotation.[Quotation Status]) Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box & "*")) Or (((TblQuotation.Requote) Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box & "*"));

 

[Golom]

kinda messy but

SELECT [Quotation Number]
     , [Raised By]
     , Customer, Contact
     , [Quotation Description]
     , Location, Product
     , [Quotation Result]
     , [Quotation Status]
     , Requote
     , [quotation value]

FROM TblQuotation 

WHERE ([Quotation Number]     Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box & "*" 
   Or [Raised By]             Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box & "*" 
   Or Customer                Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box & "*" 
   Or Contact                 Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box & "*" 
   Or [Quotation Description] Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box & "*" 
   Or Location                Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box & "*" 
   Or Product                 Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box & "*" 
   Or [Quotation Result]      Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box & "*" 
   Or [Quotation Status]      Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box & "*" 
   Or Requote                 Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box & "*")
  AND
     ([Quotation Number]      Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box2 & "*" 
   Or [Raised By]             Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box2 & "*" 
   Or Customer                Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box2 & "*" 
   Or Contact                 Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box2 & "*" 
   Or [Quotation Description] Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box2 & "*" 
   Or Location                Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box2 & "*" 
   Or Product                 Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box2 & "*" 
   Or [Quotation Result]      Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box2 & "*" 
   Or [Quotation Status]      Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box2 & "*" 
   Or Requote                 Like "*" & Forms!FrmSearchAllRecords!Search_Text_Box2 & "*")

 

[PHV]

Another way:
WHERE [Quotation Number]& [Raised By] & Customer & Contact & [Quotation Description] & Location & Product & [Quotation Result] & [Quotation Status] & Requote Like '*' & Forms!FrmSearchAllRecords!Search_Text_Box & '*'
AND [Quotation Number]& [Raised By] & Customer & Contact & [Quotation Description] & Location & Product & [Quotation Result] & [Quotation Status] & Requote Like '*' & Forms!FrmSearchAllRecords!Search_Text_Box2 & '*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dwichmann]

thank you both, worked a treat