Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    User changeable Queries (2002 - XP)

    How do I create a query that allows the user/operator to select what information is returned? I have a database of items sold, broken down by the second they are sold. I want to create a query that will allow anyone to ask, "How many of these did we sell today, last hour, last week, last month, last year?" I can get the information by going into design view and changing the criteria but, I can't seem to create a report that will allow a user to get the correct information. What I usually get is all sales added together.

    thanks for the help again.

    Bret

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User changeable Queries (2002 - XP)

    If you want something "simple" like returning all items sold between two dates, you could put parameters in the query. You would use an expression like this in the criteria row of the query grid:
    Between [Enter First Date] And [Enter Last Date]
    When the query is executed (or a report based on the query is run), Access displays an "Enter Parameter Value" dialog, with the text between the square brackets as prompt - in this case, it is displayed twice.

    But in your case, this is not flexible enough. I would create a form. On the form, put an option group with radio buttons for the various options (the Control wizard is the easiest way to create an option group); let's say you call it grpOptions. Also put a command button on the form with caption OK; I will name it cmdOK for illustration purposes.
    Create an OnClick routine for the command button. In this procedure, assemble a WHERE-condition as a string, then open the report with this WHERE-condition. The code could look like this (with the appropriate names substituted):

    Private Sub cmdOK_Click()
    Dim strWhere As String
    Select Case grpOptions
    Case 1 ' Today
    strWhere = "[SaleDate] = Date()"
    Case 2 ' Last week
    strWhere = "[SaleDate] Between Date()-7 And Date()"
    Case 3 ' Last month
    strWhere = "[SaleDate] Between DateAdd("m", -1, Date()) And Date()"
    Case 4 ' Last year
    strWhere = "[SaleDate] Between DateAdd("yyyy", -1, Date()) And Date()"
    End Select
    DoCmd.OpenReport ReportName:="rptSalesReport", View:=acViewPreview, WhereCondition:=strWhere
    End Sub

    There is more to say about this, but hopefully this will get you started.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User changeable Queries (2002 - XP)

    Thanks Hans,

    I really need to find a good resource to learn more about Access. I have taken several on line and CD based courses and they just repeat the same simple tasks.

    Access is so frustrating. Even when I get an answer from a query I never really feel confident in what it tells me.

    Bret

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User changeable Queries (2002 - XP)

    Hi Bret,

    I often felt frustrated when I was learning Access (and I still do, frequently). I have learned a lot from the sample databases you can download from Microsoft, for example Developer Solutions and Orders and Query Samples, and of course from browsing this 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
  •