Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2014
    Posts
    3

    Unanswered: report filter by form options

    i have a form to generate report with 3 filters,
    1.sales_person,
    2.Client_Name,
    3.Product_ID
    these are combo selection and the report is working fine with these 3 filters. filtering by a query. all 3 feilds on the same table.
    what i am unable to do is make these filters as option to select with a check box. like if i dont want the third filter product but to generate report with the other two filters sales_person and client_name.
    any help would be greatly appreciated.
    if any one can upload a sample that would be of great help.

    edt: uploading my DB
    Form report_generator on medical_request_query and table medical_requeset
    Attached Files Attached Files
    Last edited by Kbrokerage; 08-12-14 at 06:30. Reason: adding details

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider havign a form where you set the parameters
    on that form have a command button that opens the report (use the openreport macro) and pass those parameters as part of the 'where' parameter.
    DoCmd.OpenReport Method (Access)

    the where parameter is the 4th element and is the same as a standard SQL Where clause

    eg
    Code:
    select my, column, list from mytable where anumericcolumn >437 and userid = 'KBrokerage'
    for the where clause in the openreport macro you'd just supply
    Code:
    anumericcolumn >437 and userid = 'KBrokerage'
    to build the where clause
    Code:
    strWhereClause = "1 = 1 " 'an old dodge to make certain the whereclause is alwasy valid
    'assumes the name of the column in your db is called anumericcolumn AND is indeed a numeric column
    if not isnull(combo1.value) then 'the user selected something
      strWhereClause = strWhereClause & " AND anumericcolumn = " & combo1.value
    endif
    'assumes astringcolumn is the name of the column in your db and is indeed a string column
    'note string values MUST be delimited with a pair of ' OR " symbols
    if not isnull(combo2.text) then  'the user selected something
      strWhereClause = strWhereClause & " AND astringcolumn = '" & combo2.text & "'"
    endif
    'assumes adatecolumn is the name of the column in your db and is indeed a datetime column
    'note string values MUST be delimited with a pair of # symbols
    if not isnull(combo3.value) then  'the user selected something
      strWhereClause = strWhereClause & " AND astringcolumn = #" & combo3.value & "#"
    endif
    docmd.openreport myreportname,,strWhereClause
    of course the above is air code, its not been tested, I don't know the name of your table(s), columns, comboxes, form or report.... there will no doubt be errors and typos. its quite possibel ive used the wrong properties so you will have to delve into the code and make it work for you..... using the debugger will help you on that
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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