What's the error? What do you want to happen when TxtKeywords is blank?Is there a way to add a error check?
What's the error? What do you want to happen when TxtKeywords is blank?Is there a way to add a error check?
The error is that the query filters are all removed. I would just like to use the original query when the search is blank.
Removed? How is that possible? There is no code to modify the query.The error is that the query filters are all removed.
This code will do so:
Code:If isnull(TxtKeywords) then SQL = "Select.... " 'Original query else SQL = "Select... " 'Filter query end if me.recordsource = SQL
Last edited by davegri; 09-26-2018 at 11:56 AM. Reason: clarif
Works! Thank you very much
You do not have parentheses as shown in my example in post 11.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
This is what I have now. It seems to work
Private Sub Command433_Click()
Dim SQL As String
If IsNull(TxtKeywords) Then
SQL = "SELECT tblOrderNotifications.CreatedOn, tblOrderDetails.WBS, tblOrderNotifications.Notification, tblOrderDetails.Revision, tblOrderDetails.OrderType, tblOrderDetails.OrderNum, tblOrderDetails.Sortfield, tblOrderNotifications.CreatedBy, tblOrderStatus.AssignUsername, tblOrderDetails.PlannerGroup, tblObjectStatus.Status, tblObjectPriority.Descripton, tblProjectStatus.DateClosed, tblOrderStatus.Project FROM tblOrderNotifications LEFT JOIN (tblObjectStatus RIGHT JOIN (tblObjectPriority RIGHT JOIN (tblProjectStatus RIGHT JOIN (tblOrderDetails LEFT JOIN tblOrderStatus ON tblOrderDetails.OrderNum = tblOrderStatus.OrderNum) ON tblProjectStatus.ProjectNum = tblOrderStatus.Project) ON tblObjectPriority.PrioID = tblOrderStatus.PRIOID) ON tblObjectStatus.SID = tblOrderStatus.SID) ON tblOrderNotifications.Notification = tblOrderDetails.Notification " _
& "WHERE tblProjectStatus.DateClosed Is Null " _
& "Order BY tblOrderNotifications.CreatedOn DESC"
Else
SQL = "SELECT tblOrderNotifications.CreatedOn, tblOrderDetails.WBS, tblOrderNotifications.Notification, tblOrderDetails.Revision, tblOrderDetails.OrderType, tblOrderDetails.OrderNum, tblOrderDetails.Sortfield, tblOrderNotifications.CreatedBy, tblOrderStatus.AssignUsername, tblOrderDetails.PlannerGroup, tblObjectStatus.Status, tblObjectPriority.Descripton, tblProjectStatus.DateClosed, tblOrderStatus.Project FROM tblOrderNotifications LEFT JOIN (tblObjectStatus RIGHT JOIN (tblObjectPriority RIGHT JOIN (tblProjectStatus RIGHT JOIN (tblOrderDetails LEFT JOIN tblOrderStatus ON tblOrderDetails.OrderNum = tblOrderStatus.OrderNum) ON tblProjectStatus.ProjectNum = tblOrderStatus.Project) ON tblObjectPriority.PrioID = tblOrderStatus.PRIOID) ON tblObjectStatus.SID = tblOrderStatus.SID) ON tblOrderNotifications.Notification = tblOrderDetails.Notification " _
& "WHERE tblProjectStatus.DateClosed Is Null " _
& "And (tblOrderDetails.OrderNum) LIKE '*" & Me.TxtKeywords & "*' " _
& "Or (tblOrderDetails.WBS) LIKE '*" & Me.TxtKeywords & "*' " _
& "Or (tblOrderDetails.Sortfield) LIKE '*" & Me.TxtKeywords & "*' " _
& "Or (tblObjectStatus.Status) LIKE '*" & Me.TxtKeywords & "*' " _
& "Or (tblOrderNotifications.Notification) LIKE '*" & Me.TxtKeywords & "*' " _
& "Or (tblOrderDetails.Revision) LIKE '*" & Me.TxtKeywords & "*' " _
& "Or (tblOrderDetails.OrderType) LIKE '*" & Me.TxtKeywords & "*' " _
& "Or (tblOrderNotifications.CreatedBy) LIKE '*" & Me.TxtKeywords & "*' " _
& "Or (tblOrderDetails.PlannerGroup) LIKE '*" & Me.TxtKeywords & "*' " _
& "Or (tblOrderStatus.AssignUsername) LIKE '*" & Me.TxtKeywords & "*' " _
& "Order BY tblOrderNotifications.CreatedOn DESC"
End If
Me.RecordSource = SQL
Me.Requery
End Sub
Glad you got it working. Good luck with the rest of the project!
It should also work with the syntax (corrected parentheses) I suggested without the If Then. Should even be able to do dynamic parameterized query and eliminate VBA setting RecordSource.
However, are you sure wildcard on number fields is giving expected results? LIKE "*1*" will return anything with the digit 1 : 1, 10, 11, 12 … 100, etc.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.