Incorperate Filters into form code

[JimO]

I am trying to add a filter into my form code with no success. The simple Select statement works fine but I cannot get it into my code. The statement is:

SELECT * FROM CalendarTbl WHERE Type <> "Pers" OR Type <> "House" OR Type  <> "Gen" ORDER BY EventDay DESC;

The code right now that works is:

Private Sub RequeryForm()
    Dim W As String
    Dim S As String
   W = ""
    
    If Not IsNull(StartDate) Then
        If W <> "" Then W = W & " AND "
        W = W & "EventDay >= #" & StartDate & "#"
    End If
    If Not IsNull(EndDate) Then
        If W <> "" Then W = W & " AND "
        W = W & "EventDay <= #" & EndDate & "#"
        Me.lblTimer.Caption = "All Items in Date Range"
    End If
    If Done = True Then
        If W <> "" Then W = W & " AND "
        W = W & "Complete = True"
        Me.lblTimer.Caption = "Completed Items in Date Range"
     ElseIf Done = False Then
         If W <> "" Then W = W & " AND "
        W = W & "Complete = False"
        Me.lblTimer.Caption = "Pending Items in Date Range"   
    End If 
    
    S = "Select * FROM CalendarTbl"
    If W <> "" Then S = S & " WHERE " & W
    Me.RecordSource = S & " ORDER BY [EventDay] DESC"
      
End Sub

I though I could just modify the WHERE clause to:

If W <> "" Then S = S & " WHERE Type <> 'Pers' OR Type <> 'House' OR Type <> 'Gen' " & W

But that does not work. I would like to learn how to add this type of filter so any help would be greatly appreciated.

Thanks



Jim O

[ranman256]

instead show ALL records in the query.
then filter if needed:

    Dim W As String
    Dim S As String
   W = ""
    
    If Not IsNull(StartDate) Then
        If W <> "" Then W = W & " AND "
        W = W & "EventDay >= #" & StartDate & "#"
    End If

    If Not IsNull(EndDate) Then
        If W <> "" Then W = W & " AND "
        W = W & "EventDay <= #" & EndDate & "#"
        Me.lblTimer.Caption = "All Items in Date Range"
    End If

    If Done  Then
        If W <> "" Then W = W & " AND "
        W = W & "Complete = True"
        Me.lblTimer.Caption = "Completed Items in Date Range"
     Else
         If W <> "" Then W = W & " AND "
        W = W & "Complete = False"
        Me.lblTimer.Caption = "Pending Items in Date Range"   
    End If 
    

    If W = "" Then
       Me.filterOn =false
    else
       me.filter = W
       Me.filterOn =true
    endif

[pbaldy]

I'll point out that you have to use AND, not OR. This may also help:

http://www.baldyweb.com/ImmediateWindow.htm

[JimO]

Thank you both for the response. It appears to work although I don't yet understand it, (at my learning stage I am not sure I know enough to ask the correct questions).

I will continue to search and try to understand it.

Again thanks for your time.

Jim O

[pbaldy]

Not knowing what "it" is, hard to know what to explain.

If you mean the And/Or issue, when you use "not equal to" instead of "equal to", the logic of And vs Or flips. Given your example of:

WHERE Type <> "Pers" OR Type <> "House" OR Type <> "Gen"

If the test value is "Pers", it meets the test of <> "House", thus would be included in the result since you said "OR".