Query Prompt & Parameters (XP-SP1)

Topic

New Reply

I have a query for a report that I want to add a prompt to specifying which salesman’s records are selected. That I can do. However, how do I make it so I can get ALL the various salesmen’s records?

TIA

Reply | Quote

Replies

I found the answer and it was sooo simple. It was in a sample database I downloaded on queries from Microsoft I think.

Reply | Quote

I found the answer and it was sooo simple. It was in a sample database I downloaded on queries from Microsoft I think.

Reply | Quote

For the benefit of others would you post your solution please.

Reply | Quote

Pat, here it is. I believe that I downloaded the query sample database from MS’s KB. It is called QrySampl.mdb.

Object: Create a report that lists only one salesman’s customers or all of the customers.

In the Query, in the Salesman column in the OR row, I have:
[Enter Salesman # or for all: ]

Note: I did have a longer statement but it apparently was too long so keep that in mind.

The In created a new column in the query and copied the above parameter into the FIELD. It comes out looking like this:

Expr1: [Enter Salesman # or for all: ]

Then in this column’s CRITERIA, I entered: Is Null
In the OR row, I entered: Is Not Null

That ends the query part.

Since I wanted the report to show which salesman or if it was ALL salesman, I put the following in a text box in the header in my report:

=iif([Enter Salesman # or for all: ]>0,(“For Salesman #: ” & (Enter Salesman # or for all: ])),”ALL Salesmen”)

I’m sure there are more elegant ways to do this. However, I’m no programmer and just barely about beginner in using Access so this is the simple way and simple explanation.

Peggy

P.S. How do I put bold or italics in my posting?

Reply | Quote

Peggy,

Thanks for sharing the solution.

You can format a post by inserting tags from the 1-Click TagPanel, or by typing the tags yourself. For example, to make a word bold, put before it and after it. For italic, it’s similar with and . For example, Woody’s Lounge becomes Woody’s Lounge. You can find an overview of the available tags in Help 19.

Reply | Quote

Thanks, Hans. I knew the info was there somewhere, just didn’t know where.

Reply | Quote

Thanks, Hans. I knew the info was there somewhere, just didn’t know where.

Reply | Quote

Peggy,

Thanks for sharing the solution.

You can format a post by inserting tags from the 1-Click TagPanel, or by typing the tags yourself. For example, to make a word bold, put before it and after it. For italic, it’s similar with and . For example, Woody’s Lounge becomes Woody’s Lounge. You can find an overview of the available tags in Help 19.

Reply | Quote

Pat, here it is. I believe that I downloaded the query sample database from MS’s KB. It is called QrySampl.mdb.

Object: Create a report that lists only one salesman’s customers or all of the customers.

In the Query, in the Salesman column in the OR row, I have:
[Enter Salesman # or for all: ]

Note: I did have a longer statement but it apparently was too long so keep that in mind.

The In created a new column in the query and copied the above parameter into the FIELD. It comes out looking like this:

Expr1: [Enter Salesman # or for all: ]

Then in this column’s CRITERIA, I entered: Is Null
In the OR row, I entered: Is Not Null

That ends the query part.

Since I wanted the report to show which salesman or if it was ALL salesman, I put the following in a text box in the header in my report:

=iif([Enter Salesman # or for all: ]>0,(“For Salesman #: ” & (Enter Salesman # or for all: ])),”ALL Salesmen”)

I’m sure there are more elegant ways to do this. However, I’m no programmer and just barely about beginner in using Access so this is the simple way and simple explanation.

Peggy

P.S. How do I put bold or italics in my posting?

Reply | Quote

For the benefit of others would you post your solution please.

Reply | Quote