Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2013
    Posts
    70

    Unanswered: filter report through form

    I am continuing my search for a simple way to filter a report using a form. I tried Martin Green's approach but I ran into problems I cannot overcome. In googleing the problem icam upon a MS solution that I am working on.


    There is a filtering and a sorting version. I have the sorting version working. The "clear filter" part of the filtering version is working fine. But the filtering part is giving a problem. Here is the the code that is giving me the problem:

    Code:
    strSQL = strSQL & "[" & Me("cboFilter" & intCounter).Tag & "] " _
                 & " = " & Chr(34) & Me("cboFilter" & intCounter) & Chr(34) & "" And ""
    cboFilter1 - cboFilter6 are combo boxes from a query used to build the report rptA211Inventory. Here is the block of code the above code comes from:

    Code:
    Dim strSQL As String, intCounter As Integer
    
    For intCounter = 1 To 6
          If Me("cboFilter" & intCounter) <> "" Then
    
            strSQL = strSQL & "[" & Me("cboFilter" & intCounter).Tag & "] " _
                 & " = " & Chr(34) & Me("cboFilter" & intCounter) & Chr(34) & "" And ""
          
          End If
      Next
    
      If strSQL <> "" Then
         ' Strip Last " And ".
         strSQL = Left(strSQL, (Len(strSQL) - 5))
    I have spent a lot of time looking at this and I cannot figure out what is wrong. All the parenthesis and quotes seem to be in the right places and the right number of them. Would a date field in one of the combo boxes cause a problem? All the rest are text fields. Any help or thought are appreciated. Best, Scott

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    when you say it isn't working what actually do you mean
    the code won't compile
    the filter is being built correctly but its not returning rows

    the first step woudl be to check the strsql is valid
    ..its inmpossible to tell from the code sample as we dont' know the vlaues of the .tag properties

    strSQL = Left(strSQL, (Len(strSQL) - 5))
    looks grabage, could believe
    strSQL = mid(strSQL, (Len(strSQL) - 5))
    ...these are things that should be identifiable if you use the debug facilities


    also that test should also apply the filter if strSQL <>"" and remove the filter otherwise
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2013
    Posts
    70

    filter report through form

    Hi, the code compiles. I go back to Access and open the form and select a field value to filter on and then click the command button to filter the report. I get a debug dialog box with run time error 13 Type Mismatch (as I mentioned before, there are six fields and one is a date field the rest are text fields). When I click on debug, the following code is yellow highlighted:

    Code:
    strSQL = strSQL & "[" & Me("cboFilter" & intCounter).Tag & "] " _
                 & " = " & Chr(34) & Me("cboFilter" & intCounter) & Chr(34) & "" And ""
    The yellow arrow in the left margin is aligned with the second line of yellow highlighted code. Si it might be the line above?

    There are 6 cboFilters. Each is incremented by one. So cboFilter1, cboFilter2, etc. The tag element is from the tag line in the Other properties for the combo boxes. The tags are Date, Item, Location, Category, Level and 800. As I mentioned in my first post, this code is originally from Support.Microsoft.

    When I set a breakpoint it stops at the breakpoint, I go on and it stops at the code above and it will not beyond it. So am I correct that there is where the problem is? Or can the code

    Code:
    If strSQL <> "" Then
         ' Strip Last " And ".
         strSQL = Left(strSQL, (Len(strSQL) - 5))
        
         ' Set the Filter property.
         Reports![rptA211Inventory].Filter = strSQL
         Reports![rptA211Inventory].FilterOn = True
       End If
    not work because of the first code I inserted and that stops the [process and the debugger stops at that earlier line because that is where the second line gets it data? Thanks, Scott

  4. #4
    Join Date
    Mar 2013
    Posts
    70

    filter report through form

    In case it helps, here is the code on the sort command button that does work. It uses 6 combo boxes (cboSort1 - cboSort6) and six check boxes (check1 - check6 to set asc/desc). Best, Scott

    Code:
    Public Sub cmdSetOrderBy_Click()
        Dim strSQL As String
        Dim intCounter As Integer
        For intCounter = 1 To 6
        If Me("cboSort" & intCounter) <> "" Then
            strSQL = strSQL & "[" & Me("cboSort" & intCounter) & "]"
                If Me("Check" & intCounter) = True Then
                    strSQL = strSQL & " DESC"
                End If
            strSQL = strSQL & ", "
        End If
        Next
    
        If strSQL <> "" Then
          strSQL = Left(strSQL, (Len(strSQL) - 2))
          Reports![rptA211Inventory].OrderBy = strSQL
          Reports![rptA211Inventory].OrderByOn = True
        End If
    End Sub

  5. #5
    Join Date
    Mar 2013
    Posts
    70

    filter report through form

    OK. I have gone through BaldyWeb for a debug tutorial refresher. Here is what I think I know.

    The string:
    Code:
    strSQL = strSQL & "[" & Me("cboFilter" & intCounter).Tag & "] " _
                & " = " & Chr(34) & Me("cboFilter" & intCounter) & Chr(34) & "" And ""
    must be empty because after the code is executed. I put in:
    Code:
    Else
    MsgBox "Opps"
    after the string and before the End If. The filter code block now looks like this:
    Code:
    Private Sub cmdSetFilter_Click()
    Dim strSQL As String
    Dim intCounter As Integer
    
    ' Build SQL String.
        For intCounter = 1 To 6
            If Me("cboFilter" & intCounter) <> "" Then
    
                strSQL = strSQL & "[" & Me("cboFilter" & intCounter).Tag & "] " _
                & " = " & Chr(34) & Me("cboFilter" & intCounter) & Chr(34) & "" And ""
            
                Debug.Print strSQL
                
            Else
                MsgBox "Opps"
            End If
        Next
            
        If strSQL <> "" Then
            ' Strip Last " And ".
            strSQL = Left(strSQL, (Len(strSQL) - 5))
            ' Set the Filter property.
            Reports![rptA211Inventory].Filter = strSQL
            Reports![rptA211Inventory].FilterOn = True
        End If
    End Sub
    Now I get the MsgBox instead of a debug dialog box. That means that the line
    Code:
    If Me("cboFilter" & intCounter) <> "" Then
    is not true, so it goes to the Else and displays the MsgBox?

    When I hover
    1. over strSQL anywhere in the string I get strSQL=""
    2. In both the first and second lines intCounter gives me intCounter=4
    3. The Me in the first line does not have a tooltip popup
    4. The Me in the second line of the string's code the tooltip popup shows:
    Me("cboFilter" & intCounter) = "Immobilization Equipment"
    5. Tag in the first line does not have a tooltip popup

    The values in the popups for intCounter and Immobilization Equipment are correct data from the field I selected in the combo box on the form. Yeah?

    I tried the Debug.Print strSQL line in the code but it does not return anything. I must be doing it incorrectly. I also tried ?Print strSQL in the Immediate Window and then enter and it just drops a line. Again, I assume user error.

    If i put:
    Code:
    strSQL & "[" & Me("cboFilter" & intCounter).Tag & "] " _
                    & " = " & Chr(34) & Me("cboFilter" & intCounter) & Chr(34) & "" AND ""
    in the Immediate Window I get a "Compile Error: Expected: expression"

    Does any of this give you any ideas. Al this info does not lead me to the solution. It is probably much more informative than I understand.

    Thanks, Scott

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    y'need to develop some debug skills

    Oops as an error message is all well and good, but y'dont know why you are getting the error message. at the very least you need to understand what is going on

    personally I'd suggest you display something like
    msgbox "in Loop:" & intcounter & ", the values were:" & vbcrlf & Me("cboFilter" & intCounter) & vbcrlf & "Tag:" & Tag


    incidentally
    strSQL & "[" & Me("cboFilter" & intCounter).Tag & "] " _
    & " = " & Chr(34) & Me("cboFilter" & intCounter) & Chr(34) & "" AND ""
    isn't going to give you what you think it is
    lets assume intcounter = 1, the tag is 'mycolumn' then your code will generate and the value is "ABC"
    strSQL & "[mycolumn] = "ABC" " AND ""

    what id suggest you do to prove the code is right is to pre set your combo boxes to known good values then run the code
    check there is a value set for the tag property for each of the combo boxes
    check there is a sane value for each combo itself
    eg:-
    cbofilter1.tag = "Mycolumn" 'or whatever your column in the table is called
    cbofitler1.text = "ABC"

    cbofilter2.tag = "MyNumericColumn" 'or whatever your column in the table is called
    cbofitler1.text = 12345.65

    remember numeric values in SQL do not need delimiting, whereas text/string val;eus do with ' or " dates should; be delimited with #

    until you are upto speed I'd resist the temptation to use cunning code such building the name of the control on the fly
    right now Id suggest the old school way a separate line for each parameter, makes debugging a lot easier and your code easier to read. once you've proved it works then by all means use such code. but prove things work before going 'clever'
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2013
    Posts
    70

    filter report through form

    OK. So taking your advice to get back to old school I am trying to build each part of the sql separately. I have created all the text fields successfully. The filter works for 5 text fields. So now I am trying to add a date field. What a pain. I have read at least 20 different sites versions of how to delimit a date field. I have no idea how to use the #s or where to put them. Not one of those pages gave a really clear explanation of how to deal with dates. At least that I could understand. It is all very nebulous. So I am trying one last time to get this idea of mine to work. I need to delimit a date field in my sql.

    Do I need to delimit the date fields in both the lines that build the criteria and the where clause? If so can someone please tell me where the #s go?

    The build clause (now setup for text):
    Code:
    If IsNull(Me.cboInvDate.Value) Then
            strInvDate = "#Like '*' #"
        Else
           strInvDate = "#='" & Me.cboInvDate.Value & "#"
        End If
    The where clause (again now text):
    Code:
    strFilter = "[invDate] " & strInvDate & " AND [item] " & strItem & " AND [location] " & strLocation & " AND [category] " & strCategory & " AND [level] " & strLevel & " AND [part800Inv] " & strPart800Inv & " AND [invDate] " & strInvDate
    I am frustrated that I cannot understand dates. Please be specific. I do not have a lot of knowledge in this area. Right now every place I have tried to put the #s I get red code and a compile error:expected: end of statement.

    Thanks, Scott

  8. #8
    Join Date
    Mar 2013
    Posts
    70

    filter report through form

    Opps. posted bad code in last post. Here is the correct Where clause:

    Code:
    strFilter = "[invDate] " & strInvDate & " AND [item] " & strItem & " AND [location] " & strLocation & " AND [category] " & strCategory & " AND [level] " & strLevel & " AND [part800Inv] " & strPart800Inv
    Thanks, Scott

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if isdate(mydatecontrol) then
    strfilter = " and mydatecolumn = #" & format(mydatecontrol,"mm/dd/yyyy") & "#"
    endif

    numeric literals are not delimited, EG:-
    mydecimalcolumn = 12345.6789
    or
    strfilter = "mydecimalcolumn = " & mydecimalcontrol

    myintegercolumn = 12345.6789
    or
    strfilter = "myintegercolumn = " & myintegercontrol

    test / string values must be delimited with ' or ", although the SQL standard is '
    mystringortetcolumn = 'blah'
    or
    strfilter = "MyStringortextcolumn ='" & mystringortextcontrol & "'"

    dates must be delimited with a hash symbol #
    mydate column = #2013/11/15#
    or
    strfilter = "mydatecolumn = #" & format(mydatecontrol,"mm/dd/yyyy") & "#"

    You are making your filter more complex than it needs be by. its not needed to specify a filter if you want every row. in fact that may be causing your problems

    your current code demonstrates 2 things
    1) some confusion about how or where the date delimiter goes
    2) you haven't got to grips with the idea of debugging
    there is a world of difference between VBA code which compiles (ie the runtime accepts the code, and variables which although accepted by the compiler as valid but contains data that doesn't work. this can easily be detected by using the debugger... its why I keep grinding on about it . the debugger is there to make your life as a developer easier. rather than saying it doesn't work the debugger can actually help you identify what the fault is by examining what variables are. remember computers are thick as pigshit they will do what you tell them. You can look at vba code all day and your fallable human mind will see
    Code:
    strInvDate = "#='" & Me.cboInvDate.Value & "#"
    but your mind will read it as
    Code:
    strInvDate = "=#" & Me.cboInvDate.Value & "#"
    ..two problems there:-
    1 delimiter wrong side of the equals sign
    2 additional quote mark
    ..if code doesn't work always, always check that what you think you have coded is what the code results in
    Code:
    strInvDate = "#='" & Me.cboInvDate.Value & "#"
    is easier to diagnose when you look at the actual value in the debugger ?strInvDate results in:-
    Code:
    "#='2013/11/15#"
    ..or at least I think so

    as suggested here rather than assign each component to a separate varaible, then stitch together those components I'd suggest you build the filter as you go

    Code:
        If not IsNull(Me.cboCategory.Value) Then
            strfilter = strfilter & " and Category = '" & Me.cboCategory.Value & "'"
        End If
    as opposed to
    Code:
        If not IsNull(Me.cboCategory.Value) Then
            strfilter = strfilter & " and Category = '" & Me.cboCategory.Value & "'"
        else strfilter = strfilter  & " and Category = '*'"
        End If
    BUT bear in mind using that approach there is alway going to be a superfluous ' AND ' in front of the filter so you'd need to chop it off before using the filter

    Code:
     'we only need to apply a filter if the strfilter is set
      if len(strfilter>5 the 'we need chop off the first 5 characters, the leading " AND"
        strfilter = mid(strfitler,5) ' chop off the ' and '
        ' Apply the filter and switch it on
        With Reports![rptA211Inventory]
            .Filter = strFilter
            .FilterOn = True
        End With
      else 'do nothing, as there is no filter to apply
      'belt and braces make sure the filter is off and set to nothing
        Reports![rptA211Inventory].filteron = vbfalse
        Reports![rptA211Inventory].filter = ""
      endif
    as ever some of the above is about opinion, or 'style'. if code is easy on the eye, easily read and clearly laid out its easier to maintain, its easier to understand. I think thats is where you are at present is keep the code simple, keep it legible, keep it straightforward. if as and when you start using fancy tricks comment what you are doing. but it is only opinion, if you find a way that works for you, then stick with it, irrespecitve of that others say, assumignit wokrs

    whenever you use code that you haven't written yourself I'd strongly recommend that you set a break point on the first line of that code, then step through (using F8) and fully understand what is happening, what each element, each function call does. if you get use to using a debugger then you will NEVER go back to guess work.
    Last edited by healdem; 11-15-13 at 06:18.
    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
  •