Syntax Check

[davegri]

Is there a way to add a error check?

What's the error? What do you want to happen when TxtKeywords is blank?

[sparker75]

The error is that the query filters are all removed. I would just like to use the original query when the search is blank.

[davegri]

The error is that the query filters are all removed.

Removed? How is that possible? There is no code to modify the query.
This code will do so:

If isnull(TxtKeywords) then
    SQL = "Select....  "      'Original query
else
   SQL = "Select...   "       'Filter query
end if
me.recordsource = SQL

[sparker75]

Works! Thank you very much

[June7]

You do not have parentheses as shown in my example in post 11.

[sparker75]

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

[davegri]

Glad you got it working. Good luck with the rest of the project!

[June7]

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.