Reducing a query's results

[jedcomyn]

I have created a form that allows me to define criteria for a query (using a macro tied to the drop down box within the form).

I now want to add another drop down box that will allow me to add another criteria to the query thus refining the results.

However i would like the ability to leave one of these menus blank and still retrieve the data from the query.

I have tried to use the OR function but it expands the data results rather than reducing them. The AND function doesn't allow me to leave a menu blank i must have chosen inputs to both drop down menus.

How can i resolve this?

Jed

 

[RichO]

If you use "Like" and the wildcard "*" in your query criteria, you can still use AND with a blank field.

If you enter Like "*" & [Forms]![MyForm]![Combo1] & "*" in the criteria for one field and in the same row (AND criteria) put Like "*" & [Forms]![MyForm]![Combo2] & "*" for another field, your query will return only those records containing both criteria. However you can still leave one criteria blank and search based on the other.

 

[jedcomyn]

Thank you RichO for replying,

Have another prob. What if there is no data in a table under one of the criteria

e.g. drop down box displaying

job type then choose banker to find the bankers in the db but it does not return bankers that have no data in another search criteria i.e. Position


Why is this? and is there any thing i can do?

 

[Mile-O]

You don't need a macro.

 

[jedcomyn]

Thank you

Thanks Mileophile,

two last questions: 1) What if i want to search for just the bankers regardless of their positions? but still retain the ability to search for both.

2) How can i reset a seacrh form so that all the fields are blank ( i e New Search)


jed

 

[Mile-O]

1) What if i want to search for just the bankers regardless of their positions? but still retain the ability to search for both.

You can already do that with my example.

2) How can i reset a seacrh form so that all the fields are blank ( i e New Search)

Using the example I posted t test:

• Put a command button on your form;
• Call it cmdClear
• Set it's caption to 'New Search'
• In it's OnClick() event, select the Code Builder option;
• Put the following code:
  
  

  Private Sub cmdClear_Click()
      Me.cboJobTitles = Null
      Me.cboPositions = Null
      Me.lstResults.Requery
  End Sub

• Save it an play around

 

[jedcomyn]

Sorted...

Thank you you are my guru!!!

Jed

 

[jedcomyn]

Opening a query's results as a form

I have created a form that contains drop down boxes that are criteria for a query. When i press the command button the critiria in the form is sent to the query and the results are shown as a query window.


Is there any way that i can get the query results in another form? And if i can, is there a way of adding and taking particular results away from that form.

I want to be able to narrow the database down to say bankers and then have a list come up of the bankers in a form (the query's results) I then want to be alble to manually reduce the number of bankers ( within this new form without the main database changing.


Can anyone help?

Thank you in advance for viewing this msg

Jed