Find Record

[Dedicated]

I have a Main form with a subform. I would like to be able to find a record searching the following fields:
FunctionalArea; DeptTeam (drop down menu); Month.

This would allow me to search for the area in question, the department team it belongs to, and the month of the Audit.

Thanks,

 

[cghoga]

Create a query including the fields you need to display and in the paramater criteria of the fields you want to search by point it to the value on the search form.

So if you have a field on the search form named "FunctionalArea"
in the query in the criteria of the FunctionalArea field include something like this:

Like iif([Forms]![YourForm]![FunctionalArea].value is null,"*",[Forms]![YourForm]![FunctionalArea].value)

Once you've done this for the 3 fields you want to search by save the query.

Be sure to include a button or other method to run the query once the search criteria has been entered.

Hope this helps you.

 

[Dedicated]

So,under all three fields would I type the following:

Like IIf([Forms]![frm Dept Audit Main]![AreaListing].[value] Is Null,"*",[Form]![frm HIPPA Audit Main].[value])

Note: I had changed the fieled FunctionalAra to AreaListing.

Do I duplicate this criteria under all three fields?

Also, do I need to make create a parameter dialog box so I can enter these values for the qry?

What does the following characters do "!" "*" (is this a wildcard search?)

 

[OhioSteve]

cghoga's suggestion is good. Perhaps I can provide more explanation.

You need to put a criterion under each of the three fields. Let's start with a simplified example. A beginner's tutorial might tell you to put these values in the criteria for the query (in the query design interface):

under functionalArea [enter area]
under DeptTeam [enter team]
under month [enter month]

If you entered those exact characters (including the "[" and "]&quot and ran the query, you would get three popup message boxes. They would say "enter area", "enter team" and "enter month". Once you entered the values, the results would appear. The results would only include values that met all of the criteria.

Cghoga's suggestion is much more professional, but it uses the same basic principle. To implement his idea, you will need three text fields on the form and three matching criteria on the query.

This is his suggestion. One can translate it back into normal speech.

Like IIf([Forms]![frm Dept Audit Main]![AreaListing].[value] Is Null,"*",[Form]![frm HIPPA Audit Main].[value])

like x means "similar to x"

like iif(a=b,c,d) means "similar to this: if a=b, c, otherwise d"

like iif(form field A is null, "*", form field A)

means

similar to this: if the field is empty, the asterik character, otherwise the field itself

So now you know pretty much what his sql code does. Oh, there is a special reason why he choose the asterik character. "like *" means "like anything" in sql. So basically, if the field is empty that criterion will not apply at all.

 

[Dedicated]

Yep, a light bulb just illuminated! Cghoga & OhioSteve, Thank you so much for all your help on this subject matter. You guys are GREAT!!!!!!!

Thanks Again,

Dedicated (Monique)