Queries on multiple fields or only updated fields ?

[Wease]

I am trying to develop a "Search" function for my database that is accomplished by a Query.

I have created 3 unbound text boxes that the Query will run from. For example, I have a "Member Number" text box, a "Member Last Name" text box and a "Vehicle Make" text box. Ideally I want the user to be able to populate 1 or a combination of text boxes and have the Query run against all the entered text. So If the user only enters a "Member Number", but no other fields; the Query will return all rows that match the "Member Number". If the user enters the "Member Number" and "Member Last Name" the Query will return rows that meet both criteria.

If I use the "AND" predicate in my Query, the user has to enter all 3 boxes with the correct text to find a particular row. If the user does not populate all text boxes, the query will never return a match, because the data base has no NULL entries for any of these fields.

If I use the "OR"predicate, the user may enter 2 or fewer text boxes, but the Query will find all rows that match criteria from either box. Ideally, if the user enters 2 or more text fields, I want to Query to only match rows where both attribrutes match within the same row.

Can someone tell me how to accomplish this ??

 

[Boorgy]

Hi bud',

hmmm, well the way I do it is to check which text boxes are filled, and then have my application form the query depending on that...
It means a little more coding, but at least it works... So unless anybody has a better idea
Good luck,

boorgy

 

[jcpelejo]

Hi Wease,

1. First you have to create a query to search for the parameters that are entered into your text boxes.

SELECT [<<your table name>>].[Member Number], [<<your table name>>].[Member Last Name], [<<your table name>>].[Vehicle Make]FROM [<<your table name>>]
WHERE ((([<<your table name>>].[Member Number]) Like &quot;*&quot; & NZ([Forms]![<<your form name>>]![Member Number],&quot;&quot & &quot;*&quot AND (([<<your table name>>].[Member Last Name]) Like &quot;*&quot; & NZ([Forms]![<<your form name>>]![Member Last Name],&quot;&quot & &quot;*&quot AND (([<<your table name>>].[Vehicle Make]) Like &quot;*&quot; & NZ([Forms]![<<your form name>>]![Vehicle Make],&quot;&quot & &quot;*&quot;

2. Second, you have to create your main form with the appropriate unbound text boxes and use the source table as the &quot;Record Source&quot; for the main form. Make sure to include the &quot;Me.Requery&quot; command in the AfterUpdate() section of the text boxes.

3. Third, you have to create a subform of your query and insert it on your main form.

That should be about it.

 

[BNPMike]

You either create six queries (for the different permutations) and run one depending on which set of boxes has values, or you build a query string and run that.

I'd plump for the second option. mike.stephens@bnpparibas.com