Results 1 to 6 of 6
  1. #1
    StuartR is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    Scotland
    Posts
    46

    Using a dialog box / form to state parameters in a report

    Hi,



    I have a report which needs three parameters defined ~ [StartDate];[EndDate] and [PaymentType]

    The report was working fine, but I am now introducing a form / dialog box to specify the parameters when running the report, rather than the "not very attractive" standard Access parameter input boxes .

    My new dialog box opens up as required, and takes the input. Clicking the OK button then runs the report. But when the report runs, it still asks for input in the ugly standard boxes.

    If you just accept these boxes with no further input the report runs fine with the original input from my dialog box still being applicable.

    Any clues to why these standard boxes still show up, and how to avoid this.

    I can advise the various bits of code / macros, but prior to doing this, I wondered if there was something simple that I may have overlooked.

    Stuart

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    do this in a form.
    make a form, put in the textboxes for the 3 params.

    the query would then look like:
    select * from table where [myDate] between forms!myForm!txtStart and forms!myForm!txtEnd and [paymentType] = forms!myForm!cboPayType

  3. #3
    StuartR is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    Scotland
    Posts
    46
    Thanks Ranman

    I also mistakenly had a "parameters" line at the beginning of the SQL code and once I removed that, all was OK. However, ....

    My combobox has three selections "Subscription" "Donation" and "Other"

    Is there a way to show a 4th selection of "All" which would return all payments (no matter what type) between the selected dates ?

    Cheers

    Stuart

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It really helps if you provide names:
    What is the report name?
    What is the form name for "specifying the parameters"?
    What are the control names on the form?
    What is the SQL of the report record source?

    How are you "running" the report? A button?

    One method is to use the "WhereCondition" clause of the "OpenReport" command.
    Something like (if "PaymentType" is Text):
    Code:
    DoCmd.OpenReport "TheReport", , ,"PaymentType = '" & Me.tbPaymentType & "' AND (TheDate Between #" & Me.tbStartDate & "# AND #" & Me.tbEndDate & "#);"
    You didn't specify if "PaymentType" on the form is text or number, so I guessed Text and added the delimiters.

    Another method is to add the Where Condition to the query.



    Or you could post your dB for analysis......

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Looks like my previous post was a little late.....


    Quote Originally Posted by StuartR View Post
    My combobox has three selections "Subscription" "Donation" and "Other"
    Is there a way to show a 4th selection of "All" which would return all payments (no matter what type) between the selected dates ?
    Use a union query. See http://www.tek-tips.com/faqs.cfm?fid=2330

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    It can be done with dynamic parameterized query using LIKE with wildcard and IIf as long as the values are text type but I don't use dynamic parameterized queries. I prefer VBA as shown by ssanfu. Review http://allenbrowne.com/ser-62.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 08-01-2017, 08:10 PM
  2. Replies: 4
    Last Post: 09-03-2015, 06:01 PM
  3. Replies: 10
    Last Post: 02-14-2015, 10:41 AM
  4. Replies: 13
    Last Post: 02-04-2015, 09:42 PM
  5. Form to supply parameters to a report
    By Ray67 in forum Reports
    Replies: 11
    Last Post: 07-22-2011, 02:06 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums