Selecting records based on multiple criteria?

[HLiu]

Currently I have a piece of code

GCriteria = "DrawingNumber" & " LIKE '*" & QDrawingNumber & "*'"
Form_Main.RecordSource = "select * from tblMain where " & GCriteria

This allows the user to type in a value in the text box 'QDrawingNumber' and find all records in tblMain which has values in the field 'DrawingNumber' that match the input.

How would I go about to add multiple criteria to this query, like how would I add the line

"ProductType" & " LIKE '*" & QProductType & "*'" so that the user can specify the drawing number as well as the product type?

Thanks in advance

 

[pbaldy]

You can put " AND " between the 2 and they will both be evaluated.

 

[HLiu]

Do you mean like:

GCriteria = "DrawingNumber" & " LIKE '*" & QDrawingNumber & "*'" AND "ProductType" & " LIKE '*" & QProductType & "*'"
Form_Main.RecordSource = "select * from tblMain where " & GCriteria

This gives a 'Type Mismatch' error.

 

[pbaldy]

Close; you've closed off the quotes in the middle (probably my quotes threw you off). Try this:

GCriteria = "DrawingNumber" & " LIKE '*" & QDrawingNumber & "*' AND ProductType" & " LIKE '*" & QProductType & "*'"

And actually you've got some unnecessary concatenations going on, so try this:

GCriteria = "DrawingNumber LIKE '*" & QDrawingNumber & "*' AND ProductType LIKE '*" & QProductType & "*'"

 

[HLiu]

Hey thanks a lot! Works like a charm