Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selectable Report (2000)

    I'm pretty sure this is a doddle for you guys (and gals) but I am having a brain block.
    I have a db with 30,000 stock items purchased in various currencies then converted to $A selling price. I'd like to be able to produce a report that allows the user to do 2 things:
    1 Have a pop up of some sort as part of the report printing process that allows them to enter a percentage discount that would then be applied to all selected items ie a specialised catalogue for each customer. I can do this easily in a query but I want them to be able to do it on the run rather than have to go in and modify the query each time
    2 An additional pop up would enable them to choose particular groups of items (currently about 100 groups) so only selected items are included in the catalogue. This pop up would need to stay up so they could click on as many or as few items as required till they tell it they are finished selecting
    TIA and thanks for all the previous help
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Quakertown, PA, Pennsylvania, USA
    Posts
    517
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selectable Report (2000)

    1. You use a query to do this, but we call it a parameter query. Instead of typing a percentage into the criteria, try this instead:

    [Please enter percentage amount]

    When the query is run or when the report is run, the little message box comes up stating exactly what you typed. You can even use things like:

    Between [Enter Start Date] and [Enter End Date]

    for parameter queries, and you'll get a message box for each: start date, end date.

    2. Now that I've read the second question, I'll back up and tell you that what you want is a Query by Form. The form allows the user to enter all parameters, and a query/then report is created based on their input.

    It's not the greatest, but it's a start for you:
    http://support.microsoft.com/directory/art...B;EN-US;Q209645

  3. #3
    Lounger
    Join Date
    Mar 2002
    Location
    St Albans, Hertfordshire, England
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selectable Report (2000)

    Steve,

    Are you ready for a long one?

    The first question can be answered on it's own, or as part of an all encompasing solution.


    Instead of having the users go in and edit your queries on the fly, you can present a Parameter Input box, which is indeed a pop-up.

    To do this, go into the design of your query, and add a new column.
    In the fieldname, enter the following

    Discount: =[Enter Discount Rate, or 0 for none]

    Add a new column, and set the fieldname as ([Value] needs to be changed to whichever field holds the $A value)

    Discounted Value: [Value] * (100-([discount]/100))

    This is the same as the user entering a '5' (5%), and the sum being * 0.95

    Then just use the necessary fields in your report!


    If you want the all encompassing solution, then read below.

    Create a new, unbound form

    Add a text box control, properties as follows:
    Format: number, 2 decimal places
    Name: txtDiscount
    Default Value: 0

    Add a list box control - set the rowsource to be the table that holds all groups, include the primary key, and the description
    Set the control properties as follows:
    Name: lstReportGroups
    MultiSelect: Extended
    Default: Null (actually type in Null, it's the equiv, of selecting nothing!)

    Add a command button, properties as follows:
    Name: cmdRunReport
    Caption: Preview Report

    Under the cmdRunReport_Click event add the following code:

    Dim strSQL As String <font color=448800> 'Holds the SQL statement</font color=448800>
    Dim strWhereClause As String <font color=448800>'Holds the WhereClause</font color=448800>
    Dim varitem As Variant <font color=448800>'Holds the Selected options from the listbox</font color=448800>
    strSQL = "Select [FIELDS LIST] From [Req_Table] " <font color=448800>'Start of the SQL Statement</font color=448800>
    Dim i As Integer
    i = Me!lstReportGroups.ItemsSelected.Count <font color=448800>'Check number of Selected items</font color=448800>
    Debug.Print i
    If i = 0 Then <font color=448800>'If none (equiv of SELECT ALL)</font color=448800>
    strSQL = Left(strSQL, Len(strSQL) - 1) & ";" <font color=448800>'Close of SQL Statement</font color=448800>
    GoTo EndSQL <font color=448800>'And goto the end</font color=448800>
    End If
    If i = 1 Then <font color=448800>'If only 1 selected item</font color=448800>
    strWhereClause = "WHERE ((([Req_Table].[Group]) = '" <font color=448800>'Use the = Operator in the WHERE Clause</font color=448800>
    Else
    strWhereClause = "WHERE ((([Req_Table].[Group]) In ('" <font color=448800>'Use the IN Keyword in the WHERE Clause</font color=448800>
    End If
    For Each varitem In Me!lstReportGroups.ItemsSelected <font color=448800>'Roll through the list of selected item(s)</font color=448800>
    strWhereClause = strWhereClause & Me!lstReportGroups.ItemData(varitem) & "'" <font color=448800>'and add it to the SQL WHERE clause</font color=448800>
    If i = 1 Then <font color=448800>'If only 1 selected item</font color=448800>
    strWhereClause = strWhereClause & "));" <font color=448800>'Close the SQL Statement</font color=448800>
    GoTo EndSQL <font color=448800>'And goto the end</font color=448800>
    Else
    strWhereClause = strWhereClause & ", '" <font color=448800>'else, add the seperator</font color=448800>
    End If
    Next varitem <font color=448800>'and get the next value</font color=448800>
    strWhereClause = Left(strWhereClause, Len(strWhereClause) - 3) & ")));" <font color=448800>'and close the SQL Statement</font color=448800>
    EndSQL:
    strSQL = strSQL & strWhereClause <font color=448800>'Merge the two statements, to generate a valid statement</font color=448800>
    Debug.Print strSQL

    Dim db As Database
    Dim qDef As QueryDef
    Set db = CurrentDb <font color=448800>'set a reference to the current db</font color=448800>
    Set qDef = db.QueryDefs("Query1") <font color=448800>'set a reference to the reports query defenition</font color=448800>
    qDef.SQL = strSQL <font color=448800>'replace the stored definition with the built SQL statement</font color=448800>
    Set qDef = Nothing <font color=448800>'and clear down the references</font color=448800>
    Set db = Nothing

    DoCmd.OpenReport "rptGroupDiscounts", acViewPreview <font color=448800>'Open required report in preview</font color=448800>


    [b] All you need to do, beyond this, is incorporate the solution from the first question, into your SQL statement - drop me a line if you need any more help!!

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selectable Report (2000)

    Couldnt get it all going -( but the problem is my brain- not the suggestion! - and lack of SQL skills) but it did help me come up with an interim work around. Thank you very much for the enormous time and trouble you took in responding - it really is much appreciated
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

Posting Permissions

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