Send number of duplicates to find based on form data?

[DerekMcDonald]

Hello, friends...

How can I tell a query to find X duplicate values, where X changes based on the number of combo boxes filled out in a form? This relates to my question from yesterday about finding multiple criteria from the same field.

I have a form with three combo boxes. The user can enter up to three criteria to return records matching the criteria entered. If the user fills in only two combo boxes, I'd like the criteria for the number of duplicates to be =2. If the user fills in three combo boxes, I'd like the criteria for the number of duplicates to be =3. I'm sure this can be sent to the query with code, but I'm not sure how.

For a really lengthy explanation of what I'm trying to get at, please see my post from 3/14. This would help me out on a number of fronts, solving the dilemna of "find all X having all Y" based on a Query by Form...

Thanks!!!

 

[RickSpr]

In your form module, create a public function NumDups that returns the number of combo boxes are filled in.

Use the Find Duplicates query wizard to build your query. Then modify its "HAVING Count(*) > 1" clause, changing "1" to "Forms!FormName.NumDups()". If you want to look for exactly 1, 2, or 3 duplicates, change ">" to "=".

At least that's what I think you're looking for. It's kind of hard to visualize from your message. Rick Sprague

 

[DerekMcDonald]

Thanks, Rick! That's pretty much what finally occured to me yesterday- I created a function and string variable in a module, set the criteria field to the function, then set the string value to "2" or "3" on the "on update" event of each combo box. Works like a charm!