Applying a filter to a report

J

Joshua B

Hi,

I want the users of my database to be able to type a value in and get the
report for that value. All values and information are saved in a table, and
I've created a query with that information. For example, when I click on a
report I'll call "Gum", I want Access to prompt me with a box so that I can
put in an identifier number for a certain person. When I key in 39205, then
this report will be filtered off of a query that I've made for all numbers
but that report will give me only the information for this particular number.
Then I can see when, where, how, and why 39205 chewed this piece of gum. I
don't want to create a query or report for every single person out there.
Please help. Thank you.
 
G

George Nicholson

Several ways to do this, here are a few:

1) Use the Where argument of DoCmd.OpenReport:

If you know SomeValue and Fieldname is a text field:
DoCmd.OpenReport "ReportName", acViewPreview,,"[Fieldname] = '" &
SomeValue & "'"
.....if Fieldname is a numeric field
DoCmd.OpenReport "ReportName", acViewPreview,,"[Fieldname] = " &
SomeValue

If SomeValue is available from an open form:
DoCmd.OpenReport "Reportname", acviewPreview, "[Fieldname] = " &
Forms![formname]![controlname on form]
(if Fieldname is a text field, you'd need to use the syntax that
incorporated the additional bracketing quotes)

2) Have the query prompt the user for a parameter:

How to create a parameter query in Access 2002
http://support.microsoft.com/kb/304352/en-us

OR

How to create a query that has parameters to evaluate complex criteria in
Microsoft Access
http://support.microsoft.com/kb/290178/en-us

Simply opening a report based on such a query will trigger the prompt.

HTH,
 
Top