Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter report via form (Access 97)

    Ok, this should be simple so where am i going wrong ?

    i need to let a user filter a report recordset according to which button the click on a form. the report should show quarter 1, 2, 3, 4 according to which button was clicked.
    so far i have :
    Dim stDocName As String
    Dim strFilter As String

    stDocName = "rptQuarterExceptionsbyMT"

    strFilter = "[txtWkNo] >= "
    strFilter = strFilter & Forms![frmexceptionscontrol]![week1].Caption
    strFilter = strFilter & " And [txtWkNo] <= "
    strFilter = strFilter & Forms![frmexceptionscontrol]![week2].Caption

    DoCmd.OpenReport stDocName, acViewPreview , strFilter
    *txtWkNo is the control name in the report*
    but when this runs i get a prompt box asking me for the txtWkNo value. what have i done wrong ? The filter value looks fine if i debug.print. if i change this to exWeekNo - the table/field name then the filter seems to have no effect and all rows are returned

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Filter report via form (Access 97)

    The "filter" option in the OPenReport method is the name of a query that is used to filter the report. You want to take you string and put it as the WHERE option, like this:

    DoCmd.OpenReport stDocName, acViewPreview , ,strFilter

    And also, you may have problems using "txtWkNo". You need to refer to a field in the recordset, NOT a control in the report. So I'm guessing you should be using "WkNo=".
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter report via form (Access 97)

    Mark, thanks for the response, i think the code section above shows that i am building a where clause called strFilter which is then in the docmd.openreport method as you show, if not then please correct me ?

    regarding putting the field in the recordset into the where clause, this returns all rows (as per my last statement) then the filter is ignored and all rows are returned, regardless of the values in strFilter.

    still confused :/ <img src=/S/headthrob.gif border=0 alt=headthrob width=15 height=15>

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Filter report via form (Access 97)

    Note the difference between my OpenReport and yours. Mine has an extra comma in it. The WHERE clause is the 4th option in the command line.

    As for your string, I'd have to see what is evaluates to. What is the fieldname in your recordset? Is it WkNo, or what? Put a breakpoint in your code at the DoCmd.OpenReport line, then check the value of strFilter to see if it is what you expect.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter report via form (Access 97)

    Mark, thanks for this, it is working fine now.
    I cant believe that i didnt notice the comma, i know i missed it when i typed the code but inserted it while i was stepping through line by line, changes carried out like that are not saved so the next time i ran the code it was removed (this is what i pasted into the forum posting!). Thanks for pointing out my error ! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>. i have now moved the process to make StrFilter into a function that each button will call when clicked and it is working fine <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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