Parameter Query (2000)

Topic

New Reply

I just came from a meeting with our Marketing department and am not sure I can develop the database that they want. (I am sure I will be asking lots of questions). My question is – they want the ability to create queries on the fly. They gave me 12 fields that they want to be able to fill or not. The first field is a field called Members which has member counts. They want to be able to put in the parameter >100 (for instance). Can you do that? My next question is how would I set up a parameter query with 12 parameters where you can either fill it in or press enter and go on to the next parameter . I am looking for ideas……Thanks for any help you can provide.

Reply | Quote

Replies

The way to do this is to create a form with text boxes or combo boxes or a combination in which the user can specify the criteria. When the user clicks the “OK” command button, the WHERE condition is created dynamically. Microsoft has an example in MSKB article ACC2000: How to Filter a Report from a Pop-Up Form; it contains a link to a downloadable sample database. This does not do exactly what you want, but it will hopefully give you some ideas.
Feel free to post more questions as you go along.

Reply | Quote

I have an idea – If I create a form with the 12 fields and use Filter by Form. I know how to put a print command button that will print the results. My question is – is there a way to put the Filter by Form, Apply Filter button and Remove Filter button on the form? I need specifics since I am VBA challenged.

Reply | Quote

You can put a Filter By Form button on a form, as well as an Apply Filter button, by using the control wizard. The wizard doesn’t offer an option for a Remove Filter button, but it’s easy to create one yourself:
– Turn control wizards off.
– Place a command button on the form.
– Name it CmdRemoveFilter.
– Set its caption to Remove Filter.
– Activate the Event tab of the Properties window.
– Click in the On Click box.
– Select [Event Procedure] from the dropdown list.
– Click the … to the right of the dropdown arrow.
– Make the code look like this:

Private Sub cmdRemoveFilter_Click()
DoCmd.ShowAllRecords
End Sub

– Switch back to Access.

BUT none of these buttons, in particular the Apply Filter one, will work while Filter by Form is on. Access disables all command buttons in Filter by Form mode. The user will have to use the toolbar button or menu option to apply the filter.

Reply | Quote

That didn’t work like I thought. I kept the Remove Filter button. I made a custom toolbar with Filter by form and apply filter. When I open the form, how can I have the custom toolbar open when the form is open?

Reply | Quote

Set the Toolbar property of the form (in the Other tab of the Properties window) to the name of the custom toolbar.

Reply | Quote

Everything works but the Print button. It prints everything and not just the filtered records. I am using the following code: I am using my home computer so I have Access 2003

Option Compare Database
Dim iFilterType As Integer

Private Sub cmdPrint_Click()
Dim frm As Form
Set frm = Forms!frmFilter
If iFilterType = acApplyFilter Then
DoCmd.OpenReport “rptFilter”, acViewPreview, frm.Filter
Else
DoCmd.OpenReport “rptFilter”, acViewPreview
End If
End Sub

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
iFilterType = ApplyType
End Sub

Thanks for your help….

Reply | Quote

Change

DoCmd.OpenReport “rptFilter”, acViewPreview, frm.Filter

to

DoCmd.OpenReport “rptFilter”, acViewPreview, , frm.Filter

(WhereCondition is the 4th argument of OpenReport)

Reply | Quote

Thanks Hans – I guess I was too tired to notice a missing comma.

Reply | Quote

Another problem. I created the form and when I Filter by Form, in the dropdown list beside each field, I get two choices Null and Not Null. Shouldn’t I get the data that is in the query behind the form? I used a query for the form because I want everything at a Client level. I changed the control source to the table and still get Null and Not Null. Any ideas.

Reply | Quote

Do you have a split frontend/backend design? Filter by Form is severely limited when dealing with linked tables.

Reply | Quote

I really haven’t even got all the specifications for the database. I haven’t split the database. Would the problem be the amount of records? There are 20,000 records. Is that too many to show in a dropdown list?

Reply | Quote

Select Tools | Options…
Activate the Edit | Find tab.
Increase the number in the box “Don’t display lists where more than this number of records is read”. The default is 1,000. Increasing it to over 20,000 will slow down performance, however.

Reply | Quote

I have been asked to design a system for our Marketing Dept that I think is way over my head. I revealed this at a meeting and my manager is upset. I have done some database design but nothing real involved. They want this in 3 months time. I work in a department responsible for ad hoc reporting. I use Access to run reports and other minor functions. Mostly, I bring in data on a monthly basis and refresh the data. Overwrite a database that is on the network and people have access to it. Am I wrong to feel this is way over my head. I would like to appear confident but I am whelmed………Just would like your opinion on my feelings about this. Is 3 months unrealistic to create a database like this.

Reply | Quote

Linda, have you thought about using a list box? I’m not sure the limitations of a list box, but you can easily create a text box to filter your list box.

You could also use a list box to select your fields for your query (however adding criteria becomes increasingly difficult). I’ll attach an example of how to create a query/add criteria on the fly. (I don’t remember where I pulled this example from).

Reply | Quote

If you can work fulltime on it for three months, you should be able to accomplish a lot. But of course, it depends on the requirements for the database. If they are clear, you should be able to decide whether they are realistic, given the time and your capabilities. If the requirements are still vague, and likely to shift (unfortunately, this is often the case), it can be tricky.

Reply | Quote

As Hans notes, there are performance concerns when you start loading up dropdown lists with large number of records. However testing I have done suggests that some of the concerns date from 1994 and Access 2 when we were still running the original Pentium chips or even slower. I’ve successfully loaded as large as 60,000 records and gotten surprisingly good results on a Celeron 333 with 64 MB of RAM. Not recommended, but it may be acceptable.

Another idea you might consider: Filter by Form does have it’s limitations as you’ve been discovering. However on a regular form you can apply filters by using the right click menu and applying various kinds of filters. There you can capture the filter properties of the form, then apply them to a report based on the same data source and print the result. User driven requirements are necessary, but they often over-specify the detail level making the developer’s task much more difficult. Another alternative is to create the SQL statement used by the form and report dynamically based on an unbound form – that is more difficult than the Filter by Form approach. Hope this helps.

Reply | Quote

Linda, this is something I use to help with those extra large combo boxes with sever thousands of records in the Row Source
I have attached a database that uses a combo box that does not populate until the user enters three characters into the combo box. It will then return any matching records into the RowSource that begin with those three characters. Hopefully when the combo box is selected it will narrow down the list to a couple of hundred instead of 20,000 for you.

This is already done in my example but if you create your own…
It works from the On Change event of the combo box.
In the Property window of the combo box make sure the Auto Expand is set to Yes, clear the Control Source, and set the Row Source Type to Table/Query.
Then Change combo_box, your_field and, table_or_query in the following code to correlate with your database.

If Len(Me.combo_box.Text) > 2 Then ‘ Checks to see if there is 2 or more characters in the combo box
match = Left(Me.combo_box.Text, 3) ‘ Sets the first 3 characters for the WHERE Clause.
Me.combo_box.RowSource = “SELECT your_field FROM table_or_query ” _
& ” WHERE (((table_or_query.your_field) Like “”” & match & “*””)) ” _
& ” ORDER BY your_field;”
End If

Reply | Quote

Hi Linda

This is reasonably easy.

Create a form with the 12 Boxes. Give each a name say txtMember,txtNonMember etc

From the query that runs that form put all the relevant fields in it and use the parameters as follows:

>[Forms]![frmhhh]![txtMember]

>[Forms]![frmhhh]![txtNonMember]

Create a Report from this query.

On the form create acommand button that opens that report. Save

All the officer has to do is say type in 100 in a specific text box and it will find anything over 100

Is that what you are looking for?

Reply | Quote