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

Filtering on a date in a form

P: 7
Hi,

I want to filter the records in a form by filtering on multiple fields. The fields in the form are "ActionID", "OwnerID", "RequestorID" and "Due".
In the footer section of the form i have created following respective controls: "ActionIDFilter", "OwnerIDFilter", "RequestorIDFilter", "DueDateAfter" and "DueDateBefore".
The actual filtering gets executed after clicking the commandbutton "ApplyFiltersToToDoListForm".
Filtering runs perfect when i enter any valid value in fields "ActionIDFilter", "OwnerIDFilter" and "RequestorIDFilter".
Filtering runs not correct when i enter a valid date in fields "DueDateAfter" and "DueDateBefore".
Anyone any idea what is going wrong here ?
Thanks.
Jurgen


Expand|Select|Wrap|Line Numbers
  1. Private Sub ApplyFiltersToToDoListForm_Click()
  2.  
  3. Dim strFilter As String
  4.  
  5.     If ActionIDFilter <> "" Then
  6.         strFilter = "ActionID = " & ActionIDFilter
  7.     Else
  8.         If OwnerFilter <> "" Then
  9.             strFilter = "OwnerID = " & OwnerFilter
  10.         End If
  11.         If RequestorFilter <> "" Then
  12.             If strFilter <> "" Then
  13.                 strFilter = strFilter & " AND "
  14.             End If
  15.             strFilter = strFilter & "RequestorID = " & RequestorFilter
  16.         End If
  17.         If DueDateAfter <> "" Then
  18.             If strFilter <> "" Then
  19.                 strFilter = strFilter & " AND "
  20.             End If
  21.             strFilter = strFilter & "Due >= " & DueDateAfter
  22.         End If
  23.         If DueDateBefore <> "" Then
  24.             If strFilter <> "" Then
  25.                 strFilter = strFilter & " AND "
  26.             End If
  27.             strFilter = strFilter & "Due <= " & DueDateBefore
  28.         End If
  29.     End If
  30.  
  31.     Me.Filter = strFilter
  32.     Me.FilterOn = True
  33.  
  34. End Sub
Jan 14 '17 #1
Share this Question
Share on Google+
2 Replies


PhilOfWalton
Expert 100+
P: 1,430
Instead of code like DueDateAfter <> "" use
If Not IsNull(DueDateAfter)
Same applies to DueDateBefore

Dates must be surrounded with Hashes
So strFilter = strFilter & "Due <= " & DueDateBefore becomes
strFilter = strFilter & "Due <= #" & DueDateBefore & "#"
Ditto for DueDateAfter

Phil
Jan 14 '17 #2

P: 7
Thank you very much, Phil !
Runs nicely !
Jan 14 '17 #3

Post your reply

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