Results 1 to 3 of 3
  1. #1
    Oblio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2013
    Posts
    33

    Multiple control type Parameter Form used to filter Reports: Help with VBA Code

    Hi,



    The attached parameter form (f_Parameter)
    Is being used to filter a report based on selections in a listbox and course type. I have not added the "date from and date to" textboxes yet.

    I keep getting data type mismatch errors when I click on the preview reports button (which is using the report copy of r_MCR_report)

    When I open the parameter query in design view, it has passed the correct values to the parameter form. I have tried deleting one or the other and still get a data mismatch. I have changed their data types to integer from string and that didn't work either. Is there anyone out there that could help me with this???

    Thanks,

    William
    Attached Files Attached Files
    Last edited by Oblio; 02-27-2017 at 10:17 AM. Reason: Revision to database and new error

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,608
    There are a couple of errors I found:

    Code:
    ' Build criteria string for Instructors
        For Each varItem In Me.lst_Instructors.ItemsSelected
            strInstructors = strInstructors & ", " & Me.lst_Instructors.ItemData(varItem)
        Next varItem
        If Len(strInstructors) = 0 Then
            strInstructors = "Like '*'"
        Else
            strInstructors = Right(strInstructors, Len(strInstructors) - 1)
            strInstructors = "IN(" & strInstructors & ")"
        End If
    ' Build criteria string for CourseType
        For Each varItem In Me.lst_Course_Type.ItemsSelected
            strCourseType = strCourseType & ", " & Me.lst_Course_Type.ItemData(varItem)
        Next varItem
        If Len(strCourseType) = 0 Then
            strCourseType = "Like '*'"
        Else
            strCourseType = Right(strCourseType, Len(strCourseType) - 1)
            strCourseType = "IN(" & strCourseType & ")"
        End If
    ' Build criteria string for Date From and Date To One day maybe :\
    ' Build SQL statement
        strSQL = "SELECT q_jt_MCR_Instructor_Roles.* FROM q_jt_MCR_Instructor_Roles" & _
                 " WHERE q_jt_MCR_Instructor_Roles.[InstructorID] " & strInstructors & _
                 " AND q_jt_MCR_Instructor_Roles.[Course_TypesID] " & strCourseType & ";"
    A space is missing before the WHERE
    AND is missing before the second part of the WHERE clause
    Instructor_ID is Numeric so no quotes around the number, plus you are referring to last name but putting in the number (I used the number)

    Query Q_Parameter_Form uses the field Course_Type_Abbreviation, but it has a number (as text) in the criteria. The abbreviations don't have numbers there. Change it to the ID, and don't use the quotes.

    This should help a bit - I can get it to give me the report.

  3. #3
    Oblio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2013
    Posts
    33
    MCR Scan Name v 6.zipThank you so much! I have been working on getting it to work and am wondering if you have any advice on how to add a searchable Date Range to the sql string that is being built...

    I did add typical "Between [DateStart] AND [DateEnd] with the forms references and it works but I am wondering if there is a way to do it in the SQL String that is being built? I have attached what I have worked on so far...


    But thank you again very much for your help... it was making me crazy !!!!! So frustrating but then so wonderful when it works!! No pain no gain I guess...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 10-04-2016, 11:56 AM
  2. Passing a control as a parameter to a code module.
    By MatthewGrace in forum Programming
    Replies: 4
    Last Post: 06-20-2014, 11:14 PM
  3. Multiple Form Filter Criteria vba code
    By Moonman in forum Programming
    Replies: 6
    Last Post: 11-16-2013, 12:42 AM
  4. VBA code multiple export to xml with filter
    By Bertrand82 in forum Programming
    Replies: 9
    Last Post: 01-15-2013, 03:55 AM
  5. Use form control value as parameter for query
    By jpkeller55 in forum Access
    Replies: 2
    Last Post: 02-07-2012, 06:09 PM

Tags for this Thread

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 - Senior Forums