[X]My Assistant

[Nuke_It_Newport]

Hi everyone-

I could use some help with a dynamic query that is based on entries from a form. Some of you will recognize this from a previous post, and a different issue. I've modified things a bit.

I can search with any combination of parameters on the form "frmTaskReportCenter" (excluding the "txtFreeText" field), using the "ALL" or "ANY" operator (selected from cboOperator on the form), and it filters correctly. If I type something in the "txtFreeText" textbox on the form, whether I input other search criteria or not, using the "ALL" operator, I get no results. If I do this using the "ANY" operator, everything filters correctly. I think the problem may be in my code, where I'm building the SQL string. Here is a sample SQL statement that returns no results, even though the criteria is valid for the table "tblTasks". The code gets the entries from all the controls on the form that have values, and assembles them into a string. It uses this string as a WHERE clause for the SQL statement for the query "qselTRC". It inserts the "AND or "OR"operator at the appropriate places in the string based on the users selection of "ANY" or "ALL" on the form.

SELECT tblLocationLkp.LocationLkpID, tblLocationLkp.Location, tblPriorityLkp.PriorityLkpID, tblPriorityLkp.Priority, tblStatusLkp.StatusLkpID, tblStatusLkp.Status, tblTasks.Summary, tblTasks.Details, tblTasks.DateCreated, tblTasks.DueDate, tblTasks.WorkOrder, tblTasks.Attachments, tblTasks.PriorityID, tblTasks.LocationID, tblTasks.StatusID, tblTasks.TaskID, qselCondoForm.Calc_DD, qselCondoForm.Calc_Status
FROM tblStatusLkp
RIGHT JOIN (tblPriorityLkp
RIGHT JOIN (tblLocationLkp
RIGHT JOIN (tblTasks
INNER JOIN qselCondoForm
ON tblTasks.TaskID = qselCondoForm.TaskID)
ON tblLocationLkp.LocationLkpID = tblTasks.LocationID)
ON tblPriorityLkp.PriorityLkpID = tblTasks.PriorityID)
ON tblStatusLkp.StatusLkpID = tblTasks.StatusID
WHERE (((tblPriorityLkp.[PriorityLkpID])=1)
AND ((tblLocationLkp.[LocationLkpID])=98)
AND ((tblTasks.[Summary]) Like '*Taleb*')
AND ((tblTasks.[Details]) Like '*Taleb*'));

Here is the code that builds the SQL string:

CODE

[/code]
Private Sub cmdApplyFilter_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim b As Integer
Dim c As Integer
Dim i As Integer
Dim j As Integer
Dim z As Integer
Dim intLenSQL As Integer
Dim intLenOperator As Integer
Dim lngLen As Long
Dim lngFilteredCount As Long
Dim lngRecordCount As Long
Dim Pos As Long
Dim strWhere As String
Dim SQL As String
Dim strCombo As String
Dim strOperator As String
Dim strText As String
Dim strSQL_New As String
Dim strSQL_Org As String
Dim TableToSearch As String
Dim ValueCrnt As String
Dim ValueNew As String
Dim sWords As Variant
Const conJetDate = "\#mm\/dd\/yyyy\#"
TableToSearch = "tblTasks"
   On Error GoTo errHandler
'
' ********************************************************************************
****
' Bring In The Original QueryDef (Instantiate It)
' ********************************************************************************
****
Set db = CurrentDb()
Set qdf = db.QueryDefs(cstrQueryName)
'
' ********************************************************************************
****
' Extract The SQL from the Original QueryDef
' ********************************************************************************
****
strSQL_Org = qdf.SQL
'
' ********************************************************************************
****
' Remove the Ending ";" and Space Characters from Original SQL
' ********************************************************************************
****
intLenSQL = Len(strSQL_Org) - 3
strSQL_Org = Left(strSQL_Org, intLenSQL)
'Debug.Print strSQL_Org
'
' ********************************************************************************
****
' Close The Original Query without any Modifications
' ********************************************************************************
****
qdf.Close
Set qdf = Nothing

' ********************************************************************************
***********
' Get value of operator ("ANY" or "ALL")
' ********************************************************************************
***********
'
If Not IsNull(Me.cboOperator) Then
    If (Me.cboOperator) = "ANY" Then
        strOperator = " OR "
    Else
        strOperator = " AND "
    End If
        intLenOperator = Len(strOperator)
Else
    MsgBox prompt:="Select an operator --  ""And"" or ""Or"".", buttons:=vbOKOnly, title:=gpgMBTitle & _
    "No Operator Selected"
    GoTo exitProc
End If
'
' ********************************************************************************
***********
    'Look at each search box, and build up the criteria string from the non-blank ones.
' ********************************************************************************
***********
'
    'Text field example. Use quotes around the value in the string.
    
    If Not IsNull(Me.txtWorkOrder) Then
        strWhere = strWhere & strOperator & "WorkOrder = """ & Me.txtWorkOrder & """"
    End If
'
'    'Another text field example. Use Like to find anywhere in the field.
'    If Not IsNull(Me.txtFilterMainName) Then
'        strWhere = strWhere & "([MainName] Like ""*" & Me.txtFilterMainName & "*"") AND "
'    End If
    
    'Number field example. Do not add the extra quotes.
    
    If Not IsNull(Me.cboPriority) Then
        strWhere = strWhere & strOperator & "[PriorityLkpID] = " & Me.cboPriority
    End If
        
    If Not IsNull(Me.cboLocation) Then
        strWhere = strWhere & strOperator & "[LocationLkpID] = " & Me.cboLocation
        'vWhere = vWhere & " AND [PayeeID]=" + Me.cboPayeeID
    End If
    
    If Not IsNull(Me.cboStatus) Then
        strWhere = strWhere & strOperator & "[StatusLkpID] = " & Me.cboStatus
    End If
'
'    'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
'    If Me.cboFilterIsCorporate = -1 Then
'        strWhere = strWhere & "([IsCorporate] = True) AND "
'    ElseIf Me.cboFilterIsCorporate = 0 Then
'        strWhere = strWhere & "([IsCorporate] = False) AND "
'    End If
    
    'Date field example. Use the format string to add the # delimiters and get the right international format.
    
    If Not IsNull(Me.txtFromDateCreated) Then
        strWhere = strWhere & strOperator & "([DateCreated]) >= " & Format(Me.txtFromDateCreated, conJetDate)
    End If

    If Not IsNull(Me.txtToDateCreated) Then
        strWhere = strWhere & strOperator & "([DateCreated]) <= " & Format(Me.txtToDateCreated, conJetDate)
    End If
    
    If Not IsNull(Me.txtFromDueDate) Then
        strWhere = strWhere & strOperator & "([DueDate]) >= " & Format(Me.txtFromDueDate, conJetDate)
    End If
    
    If Not IsNull(Me.txtToDueDate) Then
        strWhere = strWhere & strOperator & "([DueDate]) <= " & Format(Me.txtToDueDate, conJetDate)
    End If
'
' ********************************************************************************
****
' Get the text from the FreeText field
' ********************************************************************************
****
'
If Not IsNull(Me.txtFreeText) Then
    Me.txtFreeText.Value = Trim(Me.txtFreeText.Value) & " "
    Me.txtFreeText.Value = Replace(Me.txtFreeText.Value, "  ", " ")
    sWords = Split(Me.txtFreeText.Value, ",")
    For z = LBound(sWords) To UBound(sWords)
        sWords(z) = Trim(sWords(z))  'remove any leading/trailing spaces
    Next z
    i = 9
    j = 10
    b = 1
    c = 2
  
     For z = LBound(sWords) To UBound(sWords) Step 1
      'Debug.Print "  " & z & "  " & sWords(z)
'
' ********************************************************************************
****
' Build the WHERE clause
' ********************************************************************************
****
'
     '**ALL Words/Fragments in any one field
'
If z = UBound(sWords) Then
             strWhere = strWhere & strOperator & " [" & CurrentDb.TableDefs("tblTasks").Fields(b).Name & "]  Like '*" & sWords(z) & "*'" & strOperator & " [" & CurrentDb.TableDefs("tblTasks").Fields(c).Name & "]  Like '*" & sWords(z) & "*'"
         Else
            strWhere = strWhere & strOperator & " [" & CurrentDb.TableDefs("tblTasks").Fields(b).Name & "]  Like '*" & sWords(z) & "*'" & strOperator & " [" & CurrentDb.TableDefs("tblTasks").Fields(c).Name & "]  Like '*" & sWords(z) & "*'" & strOperator
         End If
     Next z
    
'  If there are no matching records provide a message
'If DCount("*", "tblTasks", strWhere) = 0 Then
'    MsgBox "There are no records with those elements in the Title", vbOKOnly
'    Exit Sub
'End If
End If
'
' ********************************************************************************
****
' Check for a trailing operator in strWHERE, and remove it
' ********************************************************************************
****
Debug.Print strWhere
If strOperator = " AND " Then
strWhere = " WHERE" & Mid(strWhere, 5)
End If
If strOperator = " OR " Then
strWhere = " WHERE" & Mid(strWhere, 4)
End If
'
' ********************************************************************************
****
'Show all records if no criteria is selected.
' ********************************************************************************
****
'
'   If "WHERE" is at end of strWHERE, then remove it.
    lngLen = Len(strWhere) - 6
    If lngLen <= 0 Then
    strWhere = Left$(strWhere, Len(strWhere) - 5)
    
        MsgBox "No criteria selected. All records will be shown.", vbInformation, "Error"
        Forms.frmTaskReportCenter.Form.RecordSource = cstrQueryName
    End If
'
Debug.Print strWhere
' ********************************************************************************
****
' Set RecordSource of form to reflect search criteria
' ********************************************************************************
****
'
        strSQL_New = strSQL_Org & strWhere
        'Debug.Print strWhere
         'Debug.Print strSQL_New
        Forms.frmTaskReportCenter.Form.RecordSource = strSQL_New
        Debug.Print strSQL_New
'
' ********************************************************************************
****
' Show filtered record count of total record count
' ********************************************************************************
****
'
Me.txtRecordCount = FilterRecordCount(Me)

'Debug.Print strWhere
'Debug.Print strSQL_New
'
' ********************************************************************************
****
' Error Handling
' ********************************************************************************
****
'
exitProc:

    On Error Resume Next
    Exit Sub

errHandler:
    Call GlblErrMsg(intErr:=Err, intErl:=Erl, strErrFrm:=Screen.ActiveForm.Caption, strCtl:="cmdApplyFilter_Click")
    Resume exitProc
    Resume

End Sub
[code]

I'm not sure if this is a code problem, or if my query is set up incorrectly.
When you open the file, either navigate to the "Control Panel", click the lock icon, and enter the password "aep', or hit ALT / F11 and run the sub "UL" to show the ribbon, etc. Navigate to form "Task Report Center" to test it out, if you're interested. Enter the word "Taleb" in the "Keyword" field, select the "ALL" operator. No results should be seen. Change the operator to "ANY", and you should see 2 results with the name "Taleb".
Thanks for your help.

Attached File(s)

 Task_Journal_08.05.2018.zip ( 620.43K )Number of downloads: 11

 

[cheekybuddha]

Hi,

You need to 'OR' the free text criteria and surround them in brackets.

Build your SQL to look like:

CODE

' ...
WHERE tblPriorityLkp.[PriorityLkpID] = 1
  AND tblLocationLkp.[LocationLkpID] = 98
  AND (tblTasks.[Summary] Like '*Taleb*' OR tblTasks.[Details] Like '*Taleb*');

It would be easier/clearer to use aliases for the table names as well.

hth,

d

[cheekybuddha]

Also double-check the criteria regarding dates.

It's likely that you want 'AND' in between them, even if the main operator is 'OR'.

eg:

CODE

' ...
WHERE tblPriorityLkp.[PriorityLkpID] = 1
   OR tblLocationLkp.[LocationLkpID] = 98
   OR ([DateCreated] >= #2018-08-01# AND [DateCreated] <= #2018-08-05#)
' ...

[Nuke_It_Newport]

Thank you!! I changed both of those things, and it filters correctly. I've been building this form for about 4 months, and it finally works. Now I can move on to other things. I'm not proud that it took me that long, but this is my first experience with Access.