Search Filter with Fixed Criteria

[sparker75]

Hello,

In my last post we finished the search bar for my continuous form. What I would like to add to this is a fixed Criteria within the query.


For example if I have a fixed criteria (tblOrderDetails.WBS) LIKE '*CR*" and enter 65 in my txtKeyword text box then it should show me records that have CR in the WBS column and 65 in any of the other columns. Would this be possible with the existing code or would it need to be array?

Thanks for your help!

Here is the code as it stands


Private Sub Command433_Click()

Dim SQL As String

SQL = "SELECT tblOrderNotifications.CreatedOn, tblOrderDetails.WBS, tblOrderNotifications.Notification, tblOrderDetails.Revision, tblOrderDetails.OrderType, tblOrderDetails.OrderNum, tblOrderDetails.Sortfield, tblOrderNotifications.CreatedBy, tblOrderDetails.PlannerGroup, tblObjectStatus.Status, tblObjectPriority.Descripton FROM tblOrderNotifications LEFT JOIN (tblObjectStatus RIGHT JOIN (tblObjectPriority RIGHT JOIN (tblOrderDetails LEFT JOIN tblOrderStatus ON tblOrderDetails.OrderNum = tblOrderStatus.OrderNum) ON tblObjectPriority.PrioID = tblOrderStatus.PRIOID) ON tblObjectStatus.SID = tblOrderStatus.SID) ON tblOrderNotifications.Notification = tblOrderDetails.Notification " _
& "Where (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 & "*' " _
& "Order BY tblOrderNotifications.CreatedOn DESC"
Me.RecordSource = SQL
Me.Requery

End Sub

[June7]

Yes, you can have the static parameter in place of dynamic reference.

[sparker75]

I would like to do both. I'm just wondering how?

[sparker75]

When I make one of the dynamic references static it does not work. I over rides it.

[June7]

Do not understand. Post attempt that doesn't work.

I don't use dynamic parameterized query. I use VBA to build filter criteria.

[Micron]

If you want to flip between criteria options, you'll need a way to pass that decision to the sql - such as a combobox or option frame with option buttons. I would create the unchanging/main sql portion and assign to a variable (strSql), create an OrderBy string portion (strOrderBy) and depending on the option chosen, strCrit (criteria) is either string A or string B (or C or D....). If there are enough options, I'd use a Select Case block instead of a big IF block. According to the choice, you build and run the sql as in

Me.Recordsource = strSql & strCrit & strOrderBy

Please use code tags for more than a few lines. It will also avoid the automatic forum interjection of a space every 50 characters.

Forgot to suggest considering condensing your code by using variables and aliases when names are repeated many times. Maybe not for this time, but it can help to keep it in mind; e.g.

(ALIASING)
...FROM tblOrderNotifications As tblON...

(VARIABLE)
Dim strKW As String
strKW = "'*" & Me.TxtKeywords & "*' "

...
& "Or (tblON.Notification) = strKW

I suppose you could include the LIKE operator in the string, but I didn't want to over-complicate it, if I haven't already. Only assigning the value from Me.TxtKeywords to a variable would be another, perhaps less confusing, approach.