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.
Last edited by Micron; 08-30-2018 at 10:00 PM.
Reason: added info
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.