UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Error 3125: " Is Not A Valid Name..., Access 2016    
 
   
Nuke_It_Newport
post Jul 27 2018, 09:33 AM
Post#1



Posts: 28
Joined: 2-March 18
From: SW Michigan


Hi all-

I have a search form that doesn’t function properly. I hate asking for help without attempting to solve things myself. I have a lot of code from other people that I've learned A LOT from. I'm somewhat inexperienced with Access and SQL, and could use help. Here's what I have, and what I know.

Form: “frmTaskReportCenter”
Table: “tblTasks”
Query: “qselTRC”
Temporary query: “qselTemp”

The code creates a SQL string based on the entries from "frmTaskReportCenter", appends this string to the SQL string from "qselTRC", creates a temporary select query "qselTemp", runs this query, then deletes it. The filtered results are displayed on "frmTaskReportCenter".

Here’s the sequence of events:
• Alt – F11 and run module “UL” to show ribbon, etc.
• Navigate to form “frmTaskReportCenter”
• Select a location and / or a status from the combo boxes.
• Select “ANY” or “ALL” from the dropdown, then click “Apply”
• This action causes an Error 3215: “ is not a valid name…

Priority, Work Order, Date Created, and Due Date fields filter results correctly.
I’m not sure if this is an issue with the following syntax, or a query and / or table problem. I have tried everything I can think of, including modifying the query “qselTRC”, and changing the syntax of the following code:

CODE
' ********************************************************************************
    '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 Len(Me.txtWorkOrder & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
        
    If Not IsNull(Me.txtWorkOrder) Then
        strWhere = strWhere & "WorkOrder = """ & Me.txtWorkOrder & """" & strOperator
    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 Len(Me.cboPriority & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
        
    If Not IsNull(Me.cboPriority) Then
       strWhere = strWhere & "[Priority] = " & Me.cboPriority & strOperator
    End If
    
    
    If Len(Me.cboLocation & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
        
    If Not IsNull(Me.cboLocation) Then
       strWhere = strWhere & "[Location] = " & Me.cboLocation & strOperator
    End If
    
    If Len(Me.cboStatus & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
        
    If Not IsNull(Me.cboStatus) Then
        strWhere = strWhere & "[Status] = " & Me.cboStatus & strOperator
    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 Len(Me.txtFromDateCreated & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
    
    If Not IsNull(Me.txtFromDateCreated) Then
        strWhere = strWhere & "([DateCreated]) >= " & Format(Me.txtFromDateCreated, conJetDate) & strOperator
    End If

    If Len(Me.txtToDateCreated & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
    
    If Not IsNull(Me.txtToDateCreated) Then
        strWhere = strWhere & "([DateCreated]) <= " & Format(Me.txtToDateCreated, conJetDate) & strOperator
    End If

    
    If Len(Me.txtFromDueDate & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
    
    If Not IsNull(Me.txtFromDueDate) Then
        strWhere = strWhere & "([DueDate]) >= " & Format(Me.txtFromDueDate, conJetDate) & strOperator
    End If

    If Len(Me.txtToDueDate & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
    
    If Not IsNull(Me.txtToDueDate) Then
        strWhere = strWhere & "([DueDate]) <= " & Format(Me.txtToDueDate, conJetDate) & strOperator
    End If


Here’s the complete code:

CODE
Private Sub cmdApplyFilter_Click()

Const cstrQueryName As String = "qselTemp"
Dim sWords As Variant
Dim strWhere As String
Dim TableToSearch As String
Dim strOperator As String
Dim intLenOperator As Integer
Dim SQL As String
Dim i As Integer
Dim j As Integer
Dim z As Integer
Dim b As Integer
Dim c As Integer
Dim strSQL_New As String
Dim strSQL_Org As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim qdNew As DAO.QueryDef
Dim intLenSQL As Integer
Dim strCombo As String
Dim strText As String
Dim lngLen As Long
Dim Pos As Long
Dim ValueCrnt As String
Dim ValueNew As String
Const conJetDate = "\#mm\/dd\/yyyy\#"
TableToSearch = "tblTasks"
   On Error GoTo errHandler
'
' ********************************************************************************
****
' Delete Temporary Query If It Exists
' ********************************************************************************
****
'
If CheckQuery("qselTemp") = "Yes" Then
    DoCmd.DeleteObject acQuery, "qselTemp"
End If
'
' ********************************************************************************
****
' Bring In The Original QueryDef (Instantiate It)
' ********************************************************************************
****
Set db = CurrentDb()
Set qdf = db.QueryDefs("qselTRC")
'
' ********************************************************************************
****
' 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 = " AND "
    Else
        strOperator = " OR "
    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 Len(Me.txtWorkOrder & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
        
    If Not IsNull(Me.txtWorkOrder) Then
        strWhere = strWhere & "WorkOrder = """ & Me.txtWorkOrder & """" & strOperator
    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 Len(Me.cboPriority & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
        
    If Not IsNull(Me.cboPriority) Then
        strWhere = strWhere & "[Priority] = " & Me.cboPriority & strOperator
    End If
    
    
    If Len(Me.cboLocation & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
        
    If Not IsNull(Me.cboLocation) Then
        strWhere = strWhere & "[Location] = " & Me.cboLocation & strOperator
    End If
    
    If Len(Me.cboStatus & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
        
    If Not IsNull(Me.cboStatus) Then
        strWhere = strWhere & "[Status] = " & Me.cboStatus & strOperator
        'strWhere = strWhere & "([status] Like ""*" & Me.cboStatus & "*"") AND "
    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 Len(Me.txtFromDateCreated & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
    
    If Not IsNull(Me.txtFromDateCreated) Then
        strWhere = strWhere & "([DateCreated]) >= " & Format(Me.txtFromDateCreated, conJetDate) & strOperator
    End If

    If Len(Me.txtToDateCreated & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
    
    If Not IsNull(Me.txtToDateCreated) Then
        strWhere = strWhere & "([DateCreated]) <= " & Format(Me.txtToDateCreated, conJetDate) & strOperator
    End If

    
    If Len(Me.txtFromDueDate & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
    
    If Not IsNull(Me.txtFromDueDate) Then
        strWhere = strWhere & "([DueDate]) >= " & Format(Me.txtFromDueDate, conJetDate) & strOperator
    End If

    If Len(Me.txtToDueDate & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If
    
    If Not IsNull(Me.txtToDueDate) Then
        strWhere = strWhere & "([DueDate]) <= " & Format(Me.txtToDueDate, conJetDate) & strOperator
    End If
'
' ********************************************************************************
****
' Get the text from the FreeText field
' ********************************************************************************
****
'
If Len(Me.txtFreeText & vbNullString) Then
        If Len(strWhere) = 0 Then
            strWhere = " WHERE "
        End If
        End If

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

'Debug.Print strWhere

' ********************************************************************************
***********
' If string "strWhere" has a trailing " AND ", then delete it.
' ********************************************************************************
***********
'
ValueCrnt = "And"
ValueNew = ""

Pos = InStrRev(strWhere, ValueCrnt)

    If Pos > 0 Then
        strWhere = Mid(strWhere, 1, Pos - 1) & Replace(strWhere, ValueCrnt, ValueNew, Pos)
    Else
'
' ********************************************************************************
***********
' If string "strWhere" has a trailing " OR ", then delete it.
' ********************************************************************************
***********
'
ValueCrnt = "Or"
ValueNew = ""

Pos = InStrRev(strWhere, ValueCrnt)

    If Pos > 0 Then
        strWhere = Mid(strWhere, 1, Pos - 1) & Replace(strWhere, ValueCrnt, ValueNew, Pos)
    End If
    End If

'Debug.Print strWhere
strSQL_New = strSQL_Org & strWhere
Debug.Print strWhere
Debug.Print strSQL_New
'
' ********************************************************************************
****
' Create Temporary Query, Set RecordSource Of Form, Close & Delete Temporary Query
' ********************************************************************************
****
'
Set qdNew = db.CreateQueryDef(cstrQueryName, strSQL_New)
Set qdNew = Nothing
Set db = Nothing
DoCmd.OpenQuery (cstrQueryName)
Forms.frmTaskReportCenter.Form.RecordSource = cstrQueryName
DoCmd.Close acQuery, (cstrQueryName)
Debug.Print strSQL_New
DoCmd.DeleteObject acQuery, cstrQueryName
'
' ********************************************************************************
****

exitProc:

    On Error Resume Next
    Exit Sub

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

End Sub


I know there are other issues with the code, and probably a lot of things that I'm not aware of yet. Any help is much appreciated!

Chad
Attached File(s)
Attached File  Task_Journal_07.27.2018.zip ( 606.52K )Number of downloads: 10
 
Go to the top of the page
 

Posts in this topic
- Nuke_It_Newport   Error 3125: " Is Not A Valid Name...   Jul 27 2018, 09:33 AM
- - JonSmith   Ok, this is not a complete answer but some feedbac...   Jul 27 2018, 09:43 AM
- - orange999   I'm not sure how much of the code you "fo...   Jul 27 2018, 10:24 AM
|- - GroverParkGeorge   I think I recognize that form as coming from my de...   Jul 27 2018, 10:37 AM
|- - GroverParkGeorge   Hm. I DO like your interface better than mine.   Jul 27 2018, 10:39 AM
||- - GroverParkGeorge   Apparently, you did replace some of my original co...   Jul 27 2018, 10:49 AM
||- - GroverParkGeorge   Here's one syntax problem.   Jul 27 2018, 10:53 AM
||- - JonSmith   QUOTE I think I recognize that form as coming from...   Jul 27 2018, 11:11 AM
||- - GroverParkGeorge   It's free. I don't track downloads; I don...   Jul 27 2018, 12:01 PM
|||- - BruceM   George, I didn't know what was yours and what ...   Jul 27 2018, 12:58 PM
||- - GroverParkGeorge   Thanks for pointing out the rules violation, Jon.   Jul 31 2018, 09:00 AM
||- - JonSmith   Sorry it went to a 'Report' George, Gord t...   Jul 31 2018, 09:16 AM
||- - Nuke_It_Newport   Thanks for the help everyone. I finally got most o...   Aug 5 2018, 11:44 AM
|- - Nuke_It_Newport   Grover Park George, You are correct sir! I lea...   Jul 27 2018, 03:50 PM
|- - GroverParkGeorge   You're more than welcome. COntinued success w...   Jul 27 2018, 04:20 PM
- - BruceM   Please, compile code before posting a database. I...   Jul 27 2018, 11:51 AM
- - Nuke_It_Newport   BruceM, Sorry about that. I didn't compile bec...   Jul 27 2018, 09:07 PM
- - BruceM   The trouble with the Type Mismatch is that you wer...   Jul 30 2018, 10:36 AM



Custom Search


RSSSearch   Top   Lo-Fi    20th November 2019 - 07:32 AM