By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 921 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

Filter Report on the Fly

100+
P: 147
I have a report that I filter on the fly the following is my code, it worked before but now I added a checkbox to the filter process. I don't know how to write the code properly for the checkbox in lines 14, 15, and 16. Could someone please help me out. Thanks
Expand|Select|Wrap|Line Numbers
  1.  Private Sub FilterMemos_Click()
  2.     Dim strSQL As String, intCounter As Integer
  3.     Dim c As Access.Control
  4.     'Build SQL String
  5.     For intCounter = 1 To 2
  6.         If Me("Filter" & intCounter) <> "" Then
  7.             Set c = Me("Filter" & intCounter)
  8.             If TypeOf c Is Access.ComboBox Then
  9.                  strSQL = strSQL & "[" & c.Tag & "] " & " = " & Chr(34) & c & Chr(34) & " And "
  10.  
  11.             ElseIf TypeOf c Is Access.TextBox Then
  12.                 strSQL = strSQL & "[" & c.Tag & "] " & " = " & Chr(35) & Format(c, "mm/dd/yyyy") & Chr(35) & " And "
  13.  
  14.             ElseIf TypeOf c Is Access.CheckBox Then
  15.                 If c = True Then
  16.                    strSQL = strSQL & "[" & c.Tag & "] " & " = " & Chr(34) & c & Chr(34) & " And "
  17.  
  18.                 End If
  19.             End If
  20.         End If
  21.     Next
  22.     If strSQL <> "" Then
  23.         'Strip Last " And "
  24.         strSQL = Left(strSQL, (Len(strSQL) - 5))
  25.  
  26.         Reports!rptMilitaryLeave.Filter = strSQL
  27.         Reports!rptMilitaryLeave.FilterOn = True
  28.     End If
  29.  End Sub
Feb 11 '09 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 1,287
You need to use:
Expand|Select|Wrap|Line Numbers
  1. If c.Value = True Then
Feb 11 '09 #2

100+
P: 147
Greast I changed lines 14,15,16 to the following and now it returns no data but at least doesn't give me an error any ideas?
Expand|Select|Wrap|Line Numbers
  1.             ElseIf TypeOf c Is Access.CheckBox Then
  2.                 If c.Value = True Then
  3.                     strSQL = stFilter & " And "
Feb 11 '09 #3

100+
P: 147
Disregard I got it now it is:
Expand|Select|Wrap|Line Numbers
  1.                 If c.Value = True Then
  2.                     strSQL = strSQL & stFilter & " And "
  3.                 End If
Thank you so much that was it

Thanks
Dan
Feb 11 '09 #4

NeoPa
Expert Mod 15k+
P: 31,419
Dan,

Let me bring a couple of things to your attention.
  • I noticed you tried to use the [ CODE ] tags. That's good as we do insist on their use here at all times. The trick is to use [] around CODE rather than <>. There is also a button provided in the post page (looks like a hash (#)), which will do it for you easily.
  • The .Value property is the default property so it is not required in the code. Sometimes people choose to use it anyway, to make their code clearer to read. It's a choice, but not necessary.
  • A CheckBox actually returns a boolean value. That is - either True or False (Some allow Null too if they're tri-state ones). As such it is an exception to the rule that booleans should be used alone. As in they should NOT properly be compared to True or False. For a boolean it is simply pointless. For other data types it can be quite wrong (and a habit that's good to avoid).

    Consider an Integer variable (intTest) that has the value 4. In boolean logic this is considered equivalent to True (Anything not equal to False is considered true). Thus :
    Expand|Select|Wrap|Line Numbers
    1. intTest = 4
    2. If intTest Then Call MsgBox("Good Test")
    3. If intTest = True Then Call MsgBox("Bad Test")
    will only pop up one MsgBox saying "Good Test".
  • Numerically, False = 0 & True = -1 (all 1 bits).
  • "If" statements can span multiple lines in either of two ways :
    1. As a multi-line "If" statement, which requires an "End If" line.
    2. As a single-line "If" statement, but with a continuation character in it.
      Expand|Select|Wrap|Line Numbers
      1. If blah blah blah Then _
      2.     Call MsgBox("")
      NB. In this case there is no "End If", as there is logcally only one line.
Feb 13 '09 #5

100+
P: 147
Thanks NeoPa I will give it a try.
Feb 16 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.