Results 1 to 7 of 7
  1. #1
    mmethe is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Posts
    5

    Report Criteria

    Hello All,

    I have a form with several unbound controls and check boxes.

    Example:
    Date1 (Unbound Text) Date2 (Unbound Text) All Dates (Check Box)
    Car (Unbound combo) All Cars (check box)
    Driver (unbound text) All Drivers (check box)

    Another button calls the report that is based on a query with the above fields as criteria. If you select all dates and a specific driver, I dont get results.

    What is the best way to accomplish what I am trying to do? There are many combinations of report data to display.

    I have validated with msgboxes that all fields are passing the needed information.



    I hope the screen shots help

    Thanks!
    Attached Thumbnails Attached Thumbnails 1.PNG   2.PNG   3.PNG  

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    When all of your criteria is on the same line in the wizard, you are AND'ing your criteria. May not be what you want, just stating a fact.

    You might get more ideas from this material by Martin Green on Dynamic Reports

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    One way of doing this is to build the report SQL in sections
    Its easier to do than it is to describe so don't get put off ....

    The overall SQL will be:
    Code:
    strSQL = strSelect & strWhere & strOrderBy
    strSelect is just SELECT followed by the list of fields
    strOrderBy is ORDER BY followed by one or more fields (if needed)

    strWhere is built up of 4 parts strCarFilter, strDriverFilter, strDateFilter & strDestinationFilter

    strDateFilter is your current date criteria (or a Between StartDate & Enddate statement)

    For strCarFilter - use the combobox value if in use otherwise if check box ticked strCarFilter = ""
    Similarly for Destination & Driver

    Then combine as follows:
    Code:
    strWhere = " WHERE (" & strDateFilter & strCarFilter & strDriverFilter & strDestinationFilter & ")"
    If you add the line
    Code:
    Debug.Print strSQL
    then you can check the result for the various combinations and tweak if necessary to fix any errors
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Use VBA instead of dynamic parameterized query. Here is another tutorial 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.

  5. #5
    mmethe is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2018
    Posts
    5
    Thank You!

    What should StrWhere look like? Mine is looking like this and is not liked.

    StrWhere = "([car_id] = " & StrCarFilter & ") AND ([dst_id] = " & StrDestFilter & ") AND ([drv_id] = " & StrDriverFilter & ") AND ([trp_date] " & StrDateFilter & ")"

    For an 'all' selection the specific filter is set to "*"

    So this would look like:
    ([car_id] = 1) AND ([dest_id] = *) AND ([driver_id] = 2) AND ([trp_date] = *)

    car_id, dest_id, drv_id, and trp_date are the fields in the query that the report is based on.

    OpenReport Button code:
    <code>
    If Me!chk_AllTime.Value = True Then
    StrDateFilter = "*"
    Else
    StrDateFilter = ">=[Forms]![frm_report1]![txt_Start-Date] And <=[Forms]![frm_report1]![txt_End-Date]"
    End If
    If Me!chk_car.Value = True Then
    StrCarFilter = "*"
    Else
    StrCarFilter = Me!cmbo_car.Value
    End If
    If Me!chk_driver.Value = True Then
    StrDriverFilter = "*"
    Else
    StrDriverFilter = Me!cmbo_driver.Value
    End If
    If Me!chk_dest.Value = True Then
    StrDestFilter = "*"
    Else
    StrDestFilter = Me!cmbo_dest.Value
    End If
    StrWhere = "([car_id] = " & StrCarFilter & ") AND ([dst_id] = " & StrDestFilter & ") AND ([drv_id] = " & StrDriverFilter & ") AND ([trp_date] " & StrDateFilter & ")"
    'MsgBox StrDateFilter
    MsgBox StrWhere
    DoCmd.OpenReport "rpt_Data", acViewPreview, , StrWhere
    </code>

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What are the data types for your 4 fields (e.g.StrCarFilter)? If they are numeric, delimiters are not required. If they are text or date, proper delimiters are required. I don't want to propose an alternate example without knowing. If you would like a link on naming things, I can provide one or you can google your own. I ask because typically, I would not prefix with Str for everything. To me, it means it's a string (text) data type. If it's not, a better name would mean you'd never have to guess and neither would we! For example, if it's numeric, then intType, lngType, dblType, sngType, etc. not StrCarType.

    Also please use code tags and good indentation to make your code easier to read.
    Other tips:
    use Me. not Me!.
    ensure Option Explicit is at the top of every module
    it isn't necessary to include a property for a control reference when that property is the default property. So for textbox, combobox, .Value isn't required.
    Another example of this is If Me.chk_driver.Value = True Then
    is the same as
    If Me.chk_driver Then (as long as you're expecting that If statement to pertain to what to do if True).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Use [ ] for code tags, not <>. What does 'not liked' mean - error message, wrong results, noghing happens?
    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: 5
    Last Post: 01-30-2018, 01:16 PM
  2. Replies: 8
    Last Post: 05-29-2015, 11:52 AM
  3. Report criteria
    By Ajmarti82 in forum Reports
    Replies: 1
    Last Post: 01-03-2014, 02:09 AM
  4. Report with 3 criteria
    By BFlat in forum Reports
    Replies: 3
    Last Post: 08-01-2012, 12:17 PM
  5. Want certain criteria to appear on report.
    By kenanselo in forum Reports
    Replies: 4
    Last Post: 01-18-2010, 02:48 PM

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