Just a Basic filter that's All 3

[scotaru]

Over the past year, my company has changed from Alpha5 database to Access. I am like a goose in a snow storm. In Alpha, I could produce any report that I wanted with ease. Filter was never an issue. All I want to do is make a report from a table, filter it by a field that produces the values that I want. Do I have to make a query every time? What if I want a report from a table to produce results from field "A" that are blank?

Argghh...any help is appreciated

 

[tracy1234]

I think your best bet would be to create a form based on your table and then apply filters at will.

-Tracy

 

[scotaru]

how do I do that? If you don't mind the "noobness" of my question.

 

[tracy1234]

Use the form wizard to create a form based on your table. A simple tabular form should do. Then go to records/filter. The simplest filter is a filter by selection. Say you wanted all the records that were blank for a field called name. You just rest your mouse in the name field where it is blank and click the filter by selection. This filters the form so that it shows only records with a blank name field. This works for partial field values too.

-Tracy

 

[scotaru]

Great! Got that! But what if I want to produce labels or a print out

 

[tracy1234]

Ok, just to answer your last question, it is possible to print out a form that has been filtered.

But, I'd like to make something clear first. Your original post gave me the idea that you had a single table with some information that you'd like to filter in various ways to see various results. This is why I recommended using a form with a filter. Generally when someone says they want to see different views of the same data, I think of filtering a form. Usually printing is not necessary.

The alternative to using a filtered form is to create a query using your table as the source for the data. In the query you'd specify your query criteria (what you may be referring to as your filter).

Try to create a new query in design mode. Add the table(s) you want to pull data from. Join the tables. Pick the fields you wish to display. Where it says "Criteria" under the field name, add your (filtering) criteria.

You can then use this query to create your report.


-Tracy

 

[RoyVidar]

Don't know if it applies in this situation, but here's a way to combine form and report filters...

If the recordsource of the form and the report is the same (same table or query) it's possible to pass the form filter to the report. Either put the filter as a where condition in the openreport statement, or try using the openargs. For instance alter the ordinary wizard created openreport button code to something like this:

[tt]if me.filteron then
docmd.openreport "MyRpt",acViewPreview,,Me.Filter
else
docmd.openreport "MyRpt",acViewPreview
end if[/tt]

- if no filter is applied, all records are displayed, else only the same selection as in the form.

Roy-Vidar

 

[burcher]

Hey Roy,
I am trying to do the same thing that tracy1234 is trying to do (Let them use access filter on the form, then be able to print those results into a report) I modified the opening statement as follows:

Private Sub Command47_Click()
On Error GoTo Err_Command47_Click

Dim stDocName As String

stDocName = "Select, Patient Lookup"
If Me.FilterOn Then
DoCmd.OpenReport stDocName, acViewPreview, , Me.Filter
Else
DoCmd.OpenReport stDocName, acViewPreview
End If

Exit_Command47_Click:
Exit Sub

Err_Command47_Click:
MsgBox Err.Description
Resume Exit_Command47_Click

But I'm getting a property not found error. I'm guessing that it's in the "If Me.FilterOn Then" part, am I suppose to substitue the Me. anything? Me. means the current open form, right? Let me know if you have any ideas on what the problem might be.
Thanks Scott

 

[burcher]

Actually, I got it to work now, so thanks anyways. I'll hang on to this bit of code for a while, it's a good thing to know.