Page 1 of 3 123 LastLast
Results 1 to 15 of 41
  1. #1
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171

    Error 3075 Missing Operator in query expression

    I am using the same code for each listbox but now it is giving me an error on 2 of the listboxes. I think once I get the syntax correct for this box I can use it on the other one. All I did to make the other ones work is to change the highlighted fields.
    I have attached a copy of the error.
    Here is the coding I am using. The Category table only has two fields; CategoryID (number -Byte) and Category (text). The same is true for the Student table; StaffID (number - long integer) and Staff (text) and COD (text)

    Private Sub cmdPreviewCategory_Click()
    On Error GoTo Err_Handler
    'Purpose: Open the report filtered to the items selected in the list box.
    'Author: Allen J Browne, 2004. http://allenbrowne.com


    Dim varItem As Variant 'Selected items
    Dim strWhere As String 'String to use as WhereCondition
    Dim strDescrip As String 'Description of WhereCondition
    Dim lngLen As Long 'Length of string
    Dim strDelim As String 'Delimiter for this field type.
    Dim strDoc As String 'Name of report to open.

    'strDelim = """" 'Delimiter appropriate to field type. See note 1.
    strDoc = "Problem_Record"
    'Loop through the ItemsSelected in the list box.
    With Me.lstCategory
    For Each varItem In .ItemsSelected
    If Not IsNull(varItem) Then
    'Build up the filter from the bound column (hidden).
    strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
    'Build up the description from the text in the visible column. See note 2.
    strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
    End If
    Next
    End With

    'Remove trailing comma. Add field name, IN operator, and brackets.
    lngLen = Len(strWhere) - 1
    If lngLen > 0 Then
    strWhere = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")"
    lngLen = Len(strDescrip) - 2
    If lngLen > 0 Then
    strDescrip = "Category: " & Left$(strDescrip, lngLen)
    End If
    End If

    'Report will not filter if open, so close it. For Access 97, see note 3.
    If CurrentProject.AllReports(strDoc).IsLoaded Then
    DoCmd.Close acReport, strDoc
    End If

    'Omit the last argument for Access 2000 and earlier. See note 4.
    DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
    Exit_Handler:
    Exit Sub
    Err_Handler:
    If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreviewCategory_Click"
    End If
    Resume Exit_Handler
    End Sub
    Attached Thumbnails Attached Thumbnails Error 3075.PNG  

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Is CategoryID a text field? If so, the values must have quotes around them - IN('value1','value2'...)

  3. #3
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    CategoryID is a number field(byte)

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Look at the error message, it is showing that you are comparing CategoryID to text values - Access wants numbers there.

  5. #5
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    So how do I change the code?

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Not sure what you are trying to accomplish - shouldn't you be looking at the category field, not the category ID for your WHERE string?

  7. #7
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    I have already tried Category (tbl) in that field and it doesn't work. Do I need to indicate table and field like [Category].[CategoryID]
    I have lstCategory set up as Column 2 Column Width 1";1". But when I run the cmdPreviewCategory I get this error message.
    Attached Thumbnails Attached Thumbnails Error 3075.PNG  

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What are the two columns of the list? If the 2nd one is CategoryID then you need to specify that in your code, not sure - varitem.Column(1)? Either that or put CategoryID as the first column. Post the SQL for the listbox.

  9. #9
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    Category has 2 columns ; CategoryID (number) and Category
    I have lstCategory set up as Column 2 Column Width 1";1".
    Here is the RowSource
    SELECT category.Category FROM category ORDER BY category.Category;
    Bound Column 1
    My database is attached if you wan to see it.
    Attached Files Attached Files

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Your row source is getting only one column.

  11. #11
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    Fixed the Row Source and I am still getting prompted for a Category ID in a pop up window.

  12. #12
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    That is because that field does not exist on the report.

  13. #13
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    Ok. That makes sense. How do I fix the code to get it to run? I don't want to add StaffID, CategoryID and ProblemID to the report. Is there a way to modify the code or row source to get around this. StaffID and ProblemID already exist in my Problem_Record table. I can easily add CategoryID if this will help.

  14. #14
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Report Problem_record's recordsource has no field named CategoryID and CategoryID is in the criteria of the report.
    Secondly, the listbox for Category rowsource only selects category. It should include categoryID.
    Another problem, in table Problem, the PK is Problem ID (with a space) and is referred to as ProblemID without a space in lstProblem rowsource.
    Last edited by davegri; 02-24-2017 at 12:52 PM. Reason: more

  15. #15
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Yes, if you want to filter a table (problem_record in the report) then it is going to need all of those fields to be filtered on. You already have category so just add the id.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 09-10-2014, 11:30 AM
  2. Replies: 3
    Last Post: 03-05-2013, 11:17 AM
  3. Replies: 4
    Last Post: 07-25-2012, 04:01 AM
  4. Replies: 6
    Last Post: 05-30-2012, 12:32 PM
  5. Error 3075 Missing Operator
    By KLynch0803 in forum Queries
    Replies: 5
    Last Post: 02-11-2010, 01:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums