Results 1 to 10 of 10
  1. #1
    kloun04 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jun 2014
    Location
    Wisconsin
    Posts
    33

    Using WHERE to filter data

    My ultimate goal is to have a three reports to show specific data based on my query(ies).



    My database contains an update query (Qry: UpdateQryAttendFrm) that appends to a table (AttendanceAppendTable). My RptMtgAttendanceALL report does exactly what the name implies, it pulls all of the attendees for each of the three meeting types "SNF Coalition", "ALF Coalition" and "ALF/SNF Coalition" into one report from my QryMtgAttendance query. I would like to take this a step further and pull each meeting type separately; one report for "SNF Coalition" meetings, one report for "ALF Coalition" meetings, etc. I tried filtering my query using the WHERE clause, but I can't get it to work. I feel like I am missing a JOIN somewhere, but I can't seem to work that out. I also tried filtering in the report but that didn't work either.

    I'm going to defer to the more experienced to determine if it is better/easier to put the filter in the query or within the report. So, with that being said, I'll take your advice on whether it's better to filter at the query level or the report level (which I couldn't get to work either).

    If at the query level, then please advise how I can accomplish this. I have attached my db so you can see my objects to explain how I would filter within the query. Everything I have tried brings back zero records.
    Attached Files Attached Files

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In general terms I'd put criteria in the query if they would always be applied, in the report if it was only sometimes. By in the report I mean this type of thing:

    BaldyWeb wherecondition
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    kloun04 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Wisconsin
    Posts
    33
    pbaldy, unfortunately this didn't work when I tried it for my report; I'm still getting zero records.
    Again, I feel like I am missing a Join to one of my tables in my query, but I'm still not figuring out what it is.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What exactly didn't work? The existing query seems to pull records for all 3, as does the report, so adding a wherecondition to OpenReport using a combo or whatever to allow the user to select one of those options should limit the report to that option.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    kloun04 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Wisconsin
    Posts
    33
    Quote Originally Posted by pbaldy View Post
    What exactly didn't work? The existing query seems to pull records for all 3, as does the report, so adding a wherecondition to OpenReport using a combo or whatever to allow the user to select one of those options should limit the report to that option.

    I tried this: DoCmd.OpenReport "RptMtgAttendance SNF", , , "MeetingType2 = 'SNF Coalition'" & Me.MeetingType2 & "'"

    I still get ALL records. I am a NOVICE, who only knows how to do so much in Access, so I'm sure that is part of the problem.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Presuming the control contains the desired type it would be:

    DoCmd.OpenReport "RptMtgAttendance SNF", , , "MeetingType2 = '" & Me.MeetingType2 & "'"

    And because you used a lookup field the data is actually the number 1-3 so also try:

    DoCmd.OpenReport "RptMtgAttendance SNF", , , "MeetingType2 = " & Me.MeetingType2
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    kloun04 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Wisconsin
    Posts
    33
    I've tried putting the code everywhere I can think of and still not getting it to work. Did you get it to work in my db?
    I've tried creating new queries using different combinations, new reports, pulling from different tables. I created a Macro, I created a Module, I've tried filtering in my query, I've tried filtering in my report and all I get back is either ALL records or NO records. I'm sure this comes down to the fact that the MeetingType2 field in my AttendanceAppendTable is pulling from my Meeting Type table, but I can't seem to resolve this. I'm truly at a loss.

    SO, stupid question. Where exactly should I be putting the code you have suggested?

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I created a form with a combo based on meeting types and a button. Behind the button:

    DoCmd.OpenReport "RptMtgAttendance SNF", acViewPreview, , "MeetingType2 = '" & Me.Combo0 & "'"

    Even though the data type of the field in the meeting table is numeric, in your append table it's text so the above works. I'd have comparable data types. In meeting types it's autonumber, so in related tables it would be Number/Long.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    kloun04 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Wisconsin
    Posts
    33

    Smile

    Well, in the end I couldn't get it to work the way you did with the combo box and button (probably because I didn't know what I was doing), but I did FINALLY figure it out. All I had to do was filter MeetingType2 in my query to either "1", "2" or "3" instead of what I was trying, "SNF Coalition", "ALF Coalition", etc.

    Thanks for all of your hard work on this though, I really appreciate you trying to help me and my limited knowledge.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem, glad you got it working.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Data Filter Error
    By Kiran Prakash in forum Reports
    Replies: 5
    Last Post: 10-27-2018, 07:48 PM
  2. Using a Where Clause to Filter Data
    By purpleangelbear in forum Forms
    Replies: 1
    Last Post: 08-16-2018, 09:23 AM
  3. Use checkbox to filter data
    By acm007 in forum Access
    Replies: 5
    Last Post: 06-15-2018, 07:29 AM
  4. Replies: 7
    Last Post: 09-08-2016, 05:06 PM
  5. Replies: 1
    Last Post: 03-22-2015, 02:21 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