My Assistant
![]()
Custom Search
|
![]() ![]() |
![]() |
![]() Post#1 | |
Posts: 28 Joined: 2-March 18 From: SW Michigan ![]() | 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) |
![]() Post#2 | |
![]() UtterAccess Moderator Posts: 11,885 Joined: 6-December 03 From: Telegraph Hill ![]() | 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 |
![]() Post#3 | |
![]() UtterAccess Moderator Posts: 11,885 Joined: 6-December 03 From: Telegraph Hill ![]() | 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#) ' ... |
![]() Post#4 | |
Posts: 28 Joined: 2-March 18 From: SW Michigan ![]() | 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. ![]() ![]() ![]() |
![]()
Custom Search
|
![]() | Search Top Lo-Fi | 9th December 2019 - 12:34 AM |