Search Query Using Multiple Combo and Text Boxes

[Cat129]

Hi All

Could you help me with my querie please, I have a table with all information on it, that is input via various forms, I then have different queries pulling information from all information to run reports off. These all work fine, my problem is my 'Search Form' - below

I have created a query that finds information from 'all information' using

Like "*" & [Forms]![SearchAll F]![txtDateRasied] & "*"

This is working on all text boxes, It only half works on the combo box's when I use

Like "*" & [Forms]![SearchAll F]![combofailureanalysis] & "*"

If a selection is made in the combo box the query brings the correct results, however, if all the fields are left blank it should bring up every record, but it doesn't do this. I am certain it is the combo box's that are causing this anomoly as when I remove the combo box criteria it works perfectly again.

Any help is appreciated. Thanks.

 

[Cronk]

What does "half works" mean? Half the combos or you only get half the records?

Can you post your table structure?

 

[Cat129]

Sorry its so big

Failure Analysis, Fault Category, QA Responsible Person, Approved By, Completed By and Who is At Fault, are all combo boxes that get their information from the tables on the left

 

[Cat129]

Half work means when I use the search form with just the text fields in the query I get the correct information back. When I add the combo box's into the query using the criteria I said earlier, I get the correct results.

When I leave the combo box blank and run the query again, I get incorrect results, the query doesn't pull up all the information, it leaves some behind

 

[Cronk]

I can't see any problem there.

When you say "it leaves some behind", can you see any common factor in the records displayed/not displayed?

Also can you post the specific SQL of your selection criteria. (Go to query design and select the SQL view and copy/paste the SQL text.)

 

[Cat129]

I cant see any common factor at all, its all very confusing for me.

This is the SQL bit

SELECT [All Information].Date_Raised, [All Information].EF_Number, [All Information].[Customer_Name_(Company)], [All Information].[Supplier_Name_(Company)], [All Information].Fault_Raised_By, [All Information].Fendercare_Product_Code, [All Information].Approved_By, [All Information].Fault_Category, [All Information].Failure_Analysis, [All Information].Which_Company_Is_At_Fault, [All Information].NCR_No, [All Information].Customer_PO_Number, [All Information].Description_of_Item, [All Information].Quantity_of_Items, [All Information].QA_Reponsible_Person, [All Information].Description_Of_Fault, [All Information].Containment_Action, [All Information].Root_Cause_Analysis, [All Information].Corrective_Action, [All Information].Preventive_Action, [All Information].Completed_By, [All Information].Completed_Date
FROM [All Information]
WHERE ((([All Information].Date_Raised) Like "*" & [Forms]![SearchAll F]![txtDateRasied] & "*") AND (([All Information].EF_Number) Like "*" & [Forms]![SearchAll F]![txtEFNumber] & "*") AND (([All Information].[Customer_Name_(Company)]) Like "*" & [Forms]![SearchAll F]![txtCustomerName] & "*") AND (([All Information].[Supplier_Name_(Company)]) Like "*" & [Forms]![SearchAll F]![txtSupplierName] & "*") AND (([All Information].Fault_Raised_By) Like "*" & [Forms]![SearchAll F]![txtNCRRaisedBy] & "*") AND (([All Information].Fendercare_Product_Code) Like "*" & [Forms]![SearchAll F]![txtProductCode] & "*") AND (([All Information].Approved_By) Like "*" & [Forms]![SearchAll F]![comboapprovedby] & "*") AND (([All Information].Fault_Category) Like "*" & [Forms]![SearchAll F]![combofaultcategory] & "*") AND (([All Information].Failure_Analysis) Like "*" & [Forms]![SearchAll F]![combofailureanalysis] & "*") AND (([All Information].Which_Company_Is_At_Fault) Like "* " & [Forms]![SearchAll F]![combowhowasatfault] & "*"));

 

[Cronk]

I can't see anything obviously wrong.

Is the bound column in combo box the index which is in the table, or the text from the lookup table?

I'd suggest you trouble shoot it by removing all combo box criteria, prove that you get the results you expect, and then add the combo boxes one by one to the criteria.

Let us know how you go.

 

[MSAccessRookie]

I cant see any common factor at all, its all very confusing for me.

This is the SQL bit

SELECT [All Information].Date_Raised, [All Information].EF_Number, [All Information].[Customer_Name_(Company)], [All Information].[Supplier_Name_(Company)], [All Information].Fault_Raised_By, [All Information].Fendercare_Product_Code, [All Information].Approved_By, [All Information].Fault_Category, [All Information].Failure_Analysis, [All Information].Which_Company_Is_At_Fault, [All Information].NCR_No, [All Information].Customer_PO_Number, [All Information].Description_of_Item, [All Information].Quantity_of_Items, [All Information].QA_Reponsible_Person, [All Information].Description_Of_Fault, [All Information].Containment_Action, [All Information].Root_Cause_Analysis, [All Information].Corrective_Action, [All Information].Preventive_Action, [All Information].Completed_By, [All Information].Completed_Date
FROM [All Information]
WHERE ((([All Information].Date_Raised) Like "*" & [Forms]![SearchAll F]![txtDateRasied] & "*") AND (([All Information].EF_Number) Like "*" & [Forms]![SearchAll F]![txtEFNumber] & "*") AND (([All Information].[Customer_Name_(Company)]) Like "*" & [Forms]![SearchAll F]![txtCustomerName] & "*") AND (([All Information].[Supplier_Name_(Company)]) Like "*" & [Forms]![SearchAll F]![txtSupplierName] & "*") AND (([All Information].Fault_Raised_By) Like "*" & [Forms]![SearchAll F]![txtNCRRaisedBy] & "*") AND (([All Information].Fendercare_Product_Code) Like "*" & [Forms]![SearchAll F]![txtProductCode] & "*") AND (([All Information].Approved_By) Like "*" & [Forms]![SearchAll F]![comboapprovedby] & "*") AND (([All Information].Fault_Category) Like "*" & [Forms]![SearchAll F]![combofaultcategory] & "*") AND (([All Information].Failure_Analysis) Like "*" & [Forms]![SearchAll F]![combofailureanalysis] & "*") AND (([All Information].Which_Company_Is_At_Fault) Like "* " & [Forms]![SearchAll F]![combowhowasatfault] & "*"));

Unless you made a Type-O, there looks like there is an extra space in your last LIKE Statement that could create an invalid match (See above in RED). Maybe changing that will make a difference.

 

[Cat129]

I can't see anything obviously wrong.

Is the bound column in combo box the index which is in the table, or the text from the lookup table?

I'd suggest you trouble shoot it by removing all combo box criteria, prove that you get the results you expect, and then add the combo boxes one by one to the criteria.

Let us know how you go.

I have removed all the combo box criteria and proved that I get the correct results without them in the query. And I have tried adding the combo box's one by one, regardless of which combo box I add in first I get the same results.

I'm not sure what you mean by bound coloum in combo box (sorry im very new to all of this), Each combo box gets its information from a dedicated table. Sorry if that wasn't what you asked

 

[Cat129]

Ive just had a thought, could it be giving me strange results because 'QA Responsible Person' , 'Completed By' , and 'Approved By' all get their information from the same table? They all get their information from the QA Responsible person table. Does each lookup / combo box need its own table?

 

[Brianwarnock]

Can the fields being queried by the combos be null,
Like "*" will not return null fields , since combo searches are not partial searches change to

Field= forms!formname!Comboname or forms!formnane!comboname Is Null

Brian

 

[spikepl]

The entire set up of your table All Information is incorrect. Your table should not contain the text in the fields where you have a combo, but only the foreign key. For display, you would use a query that for each foreign key looks up the text value in the respective table. Or, on the form, a combo like you do now.

tblAllInformatiion
-----------------
NCR_No (PK)
...
CustomerID (FK- foreign key - which is a primary key in a table called tblCustomers)
...
FaultCategoryID (FK - which is a primary key in a table called tblFaultCategories)
...


f.ex.

tblFaultCategories
-------------------
FaultCategoryID (PK, autonumber)
FaultCategoryName (text)