CODEWITHSUNDEEP
ms-accessvbams-access-2010
james.sw.clark
james.sw.clark

Reputation: 357

Access - Filter by date with a checkbox

I would like to have a check-box that, when ticked, filters the loaded data to only include data where it has been at least one month until the "Expected pair maturation" date. Here is my attempt:

Private Sub LockFilter1Chk_Click()

If Me!LockFilter1Chk = True Then
Me.RecordSource = "SELECT * FROM staff " & _
WHERE DateDiff("m", Me![Expected pair maturation], Now()) > 1 "
End If

If Me!LockFilter1Chk = Not True Then
Me.RecordSource = "SELECT * FROM Staff "
End If

End Sub

Upvotes: 0

Views: 301

Answers (3)

iDevlop
iDevlop

Reputation: 25262

You did not mention what was your problem.
You can do it the way you did, but I would use an if then else construct: 

Private Sub LockFilter1Chk_Click()
    If Me!LockFilter1Chk = True Then
       Me.RecordSource = "SELECT * FROM staff " & _ 
                         "WHERE [Expected pair maturation] < DateAdd('m', -1, Date())"
    else
       Me.RecordSource = "Staff"
    End If
End Sub

Alternatively you could leave the record Source unchanged and use a filter:

If Me!LockFilter1Chk = True Then
   docmd.applyfilter , "[Expected pair maturation] < DateAdd('m', -1, Date())"
else
   me.filterOn = false
end if

Note: If you have quotes within quotes, you can also use single quotes inside: 

"DateDiff('m', Me![Expected pair maturation], Now()) > 1 "

Upvotes: 1

Gustav
Gustav

Reputation: 55831

It might be simple to set the Filter:

Private Sub LockFilter1Chk_Click()

    If Me!LockFilter1Chk = True Then
        Me.Filter = "[Expected pair maturation] < DateAdd("m", -1, Date())"
        Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If

End Sub

Upvotes: 2

Andre
Andre

Reputation: 27634

After a string and line concatenation & _ you need a new double quote to start the new string.

If you have " inside a string, you must mask it as "".

In a SQL SELECT recordsource, don't refer to Me!. You are comparing the value from the table, so only use the field name.

Some indentation helps a lot with readability.

This should be closer (not sure if it already does what you want).

Private Sub LockFilter1Chk_Click()

If Me!LockFilter1Chk = True Then
    Me.RecordSource = "SELECT * FROM staff " & _
      "WHERE DateDiff(""m"", [Expected pair maturation], Now()) > 1 "

Else
    Me.RecordSource = "SELECT * FROM Staff "
End If

End Sub

Upvotes: 1

Related Questions