Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parameter queries (AccessXP)

    I have a parameter query that is the base of a subreport. It works well on the original report. I placed it on another report. When I try to go to page two of the combined report I have to answer the parameter query again. What is going on.

    Thanks
    Fay

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Parameter queries (AccessXP)

    I've seen this kind of behavior where you have two subreports with the same query for their data source. I've also seen it in some situation where the parameter for the query hasn't been explicitly declared using the Query/Parameters dialog box. A couple of possibilities that come to mind, though there are probably several others. Hope this helps.
    Wendell

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Parameter queries (AccessXP)

    If you put the parameters as controls of a form then you should not have the problem.
    Be sure to reference the form for the parameters in the query.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter queries (AccessXP)

    I changed the parameters for the query to the Query/Parameters dialog box. Didn't work.

    I placed subrptSatementBilledAmount on subrptSatementPeople then those were placed on rptStatement2. The problem of needing to enter the parameters with each page turn shows up starting with the combination of the two suprpts. Each rpt and subrpt is based on different queries.

    Pat I am working with reports. Are you suggesting that I create a form to display prior to the parameters? Then the parameters are requested and finally used by the report?

    Any other ideas?

    Thanks. Fay

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter queries (AccessXP)

    I think you are getting two prompts for parameters as the query is being opened twice. Is the report launched from a form? If it is then provide an intermediate dialog that requests the parameters, these are then passed back to the form launching the report which fills in hidden field(s) on the form with these params. Then in the query instead of prompting for a parameter look it up on the form you launch the report from. This also has the advantage (should it be desired) of being able to prompt the user with the last selection should the report be opened again (assuming the form isn't closed in the meantime). If the report is not being handled through a form then supply a dummy intermediate form that is hidden after the params are collected and closes after the report is closed.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Parameter queries (AccessXP)

    I think Andy and Pat have you on the right track. In their solution, the query simply references a control on the form, hidden or not, which contains the criteria for your query.
    Wendell

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter queries (AccessXP)

    If I go just to the table and bypass the query for the payments then all works fine. But then I can't limit to a specific time period. So I guess I will delve into creating the form to access the parameters. I haven't had much luck with them in the past. But here goes. Thanks for the help.

    Fay

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter queries (AccessXP)

    I have created a form, frmStatementDates, which is accessed from the frmSwitchboard. The frmStatementDates has three textbox controls, ctrStart, ctrEnd, and ctrPayment. There is a command button that is coded to open the report StatementPeople. The button works. What I haven't gotten to work is get the information from Forms!frmStaatementDates!ctrPayment to go into the query or a hidden control on the report to limit to a time frame. What is the proper syntax should I be using I have used =Forms!frmStaatementDates!ctrPayment to no avail. I have used () and [].

    Any an all help would be appreciated. Thanks. Fay

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Parameter queries (AccessXP)

    What you need to do is to enter in the criteria of the query of the appropriate field Forms!frmStaatementDates!ctrPayment, and in the criteria of the other field Between Forms!frmStaatementDates!ctrStart and Forms!frmStaatementDates!ctrEnd.
    Post your query in SQL form here for us to have a look at.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Parameter queries (AccessXP)

    It is worth learning to use the expression builder to get the syntax right in situations like this.

    Right click in the criteria line, and choose build.
    Double click the + next to forms,
    Double click the + next to All forms
    Single click the form you want to use, and a list of all its controls appears in the centre panel.
    The top panel is where the final expression will be built.
    To refer to just a single control, double click it in the centre panel.

    Your case is more complicated.
    Type between in the top panel, then double click ctrStart then click the and button, then double click ctrEnd
    When the expression is correctly shown in the top panel, click OK
    Regards
    John



  11. #11
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter queries (AccessXP)

    Thanks Pat and John. I copied your code right into the query and the Between/And parameter worked as advertised. But the query for the subrpt isn't working. Here are the two SQL

    Main report StatementPeople based on the Statement query:
    SELECT tblMembershipList.MembershipID, [LastName] & ", " & [FirstName] & " " & [MiddleName] AS Name, tblMembershipList.LastName, tblMembershipList.FirstName, tblMembershipList.MiddleName, tblMembershipList.Suffix, tblAddresses.MailingAddress, tblAddresses.BusinessName, tblAddresses.Street, tblAddresses.Street2, tblAddresses.City, tblAddresses.St, tblAddresses.Zip, tblAddresses.[+4], qryStatementBilling.MonthlyInvoiceID, qryStatementBilling.DateofBilling, qryStatementBilling.Total
    FROM (tblMembershipList INNER JOIN qryStatementBilling ON tblMembershipList.MembershipID = qryStatementBilling.MembershipID) INNER JOIN tblAddresses ON tblMembershipList.MembershipID = tblAddresses.MembershipID
    WHERE (((tblAddresses.MailingAddress)=Yes) AND ((qryStatementBilling.DateofBilling) Between [Forms]![frmStatementDates]![ctrStart] And [Forms]![frmStatementDates]![ctrEnd]));


    For the subrptPayment based on the Payment query: SELECT tblPayments.MembershipID, tblPayments.MonthlyInvoiceID, tblPayments.PaymentAmount, tblPayments.PaymentDate FROM tblPayments
    WHERE (((tblPayments.PaymentAmount)=[Forms]![frmStatementDates]![ctrPayment]));

    Is there an intermediate step beyond what we have done above to get a subrpt to work? Thank you for you help.
    Thanks!
    Fay

  12. #12
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter queries (AccessXP)

    Got the payments to show up!!!! I changed the frmStatementDate form to have a short date input mask on each date control and entered single dates with a leading zero.

    Thanks to every one who has help me get here. Fay

  13. #13
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter queries (AccessXP)

    In your query where you currently have your parameters (eg. [dteFrom]) replace them with Forms!frmStaatementDates!ctrStart (ctrEnd, ctrPayment). You shouldn't need the '=' sign

Posting Permissions

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