Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2007
    Location
    Harrisburg, PA
    Posts
    44

    Unanswered: How do I suppress or skip a parameter?

    I created a form that uses a query to search for the parts that make up a kit and return information on each part in the kit. You can enter one kit at a time, or do a range of kits (ie. Starting at KIT1 through KIT5).

    I then have a report that is opened by a button on the form called "Print Preview". This report shows the same information in the query (a little nicer looking for printout) and 2 custom totals fields.

    I created a Totals Footer for the report where the 2 totals are shown. The control sources are calculated instead of tied to a tbl or qry. These are the calculated values:

    Total 1: =Sum([qry-ALL_KIT_INFO]!CostOfKit)
    Total 2: =Sum([qry-ALL_KIT_INFO]!PriceOfKit)

    If I open the report without using the form, it asks me for 3 Parameters. This is the problem...

    Parameter one is the First Kit number I want to enter, which works
    Parameter two is the Ending Kit number on the range, which also works
    Parameter three asks for Totals. I have no field called totals nor any calculated value called totals. I leave this parameter null and it works correctly. The value does not show up anywhere and seems un-needed. What is this for and how can I suppress it or bypass it (can a Macro be used to hit the enter key to just hae the user skip over?

    Thank you for anything you come up with. I can give more detail if it is required...

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you get prompted for a parameter you're not specifically wanting to input as criteria, check the field name in the query or the ControlSource for the field on the report. I'm guessing you have a controlsource on the report which has =[Totals] or possibly in the Sorting/Grouping box for the report (or even in the Conditional Formatting for a field). Or in the query. Somewhere you have an expression or something with the wording: Totals and Access thinks this is a field (this is usually bracketed [] - [Totals]). Sometimes people will put for the controlsource of a field on a report =Totals "For September" (accidently not putting in the " around the whole sentence) and access thinks Totals is a field name. You should correct any parameter boxes you get prompted for which aren't supposed to show.

    Keep in mind that a summization query where you sum a field, Access usually puts in SumOfFieldName if no expression name is specified. If your report is based on a summization query where you had totalled on a field called Totals (and you didn't specify the expression name), then in the report Access thinks it's called: SumOfTotals from the query so you probably have a field on the report where the controlsource =Totals and it should be SumOfTotals. Display the Field List box (i.e. View -> Field List) in the report design to see the fieldnames Access recognizes from the query.

    Also, if you want to leave the criteria blank, you can put in criteria:

    Like [Enter the Value:] & *

    You can then leave the criteria blank and you will get all records. Doing this though does NOT return records which have a blank (or null) value. So if you want these values, I've often found I will create a 2nd query where I have criteria (or there is another trick to handling these in the code bank):

    Like [Enter the Value:] & * or Is Null

    Also keep in mind if it's an integer field and you use the Like in the criteria, you may get records you don't want (ex: You have criteria: Like [Enter the Value:] & *) for criteria on an integer field and you enter 1. You will get all the records which begin with 1 (i.e. 1, 13, 14, 15, etc...)

    I usually like to design a form for criteria. Here's an example (from the Code bank) on how to do criteria from a form on a report for a date range: http://www.dbforums.com/showpost.php...1&postcount=21

    For a number field and if you want to do a range, you can also use the Between (i.e. Between [Enter First Number:] and [Enter Second Number:]) or you can use the >=.

    There are a lot of good examples in the code bank to do a variety of things and I recommend you download a few to see them. (Select the DBForums Code Bank - first Sticky post in the MSAccess forums here.)
    Last edited by pkstormy; 10-29-07 at 19:09.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Oct 2007
    Location
    Harrisburg, PA
    Posts
    44
    I changed the Totals footer to ="Totals" instead of Totals. It works now. Crazy how that thought it was a parameter instead of just a name.

    Thanks!

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You should be using a label if all you want is the literal word "Totals". It will save you some headaches in the future.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Oct 2007
    Location
    Harrisburg, PA
    Posts
    44
    I do have a label named "Totals" also. That was not the problem.

    In the Grouping and Sorting, I had named a footer group Totals and Access took that to mean [Totals] instead of "Totals".

    pkstormy solved my problem and then I even realized I could just throw the data I was using into a pre-existing Footer that was just not activated(visible). Now it works great!

    Thank you for your help.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ah, I assumed you were trying to put literal text on the report.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •