Query Criteria from Form poblem 1

[SmokeEater]

I have a query

SELECT tblPrepTime.Course, tblPrepTime.Instructor, tblPrepTime.Type_Of_Prep, tblPrepTime.Date, tblPrepTime.Time, tblCourseInfo.Course_Name, tblCourseInfo.[Start Date], tblCourseInfo.[End Date]
FROM tblPrepTime INNER JOIN tblCourseInfo ON tblPrepTime.Course = tblCourseInfo.Course_No
WHERE (((tblPrepTime.Type_Of_Prep)=[Forms]![frmReportOptions]![ctlPrepTypeResult]));

As you can see I am trying to get the criteria from the form frmReportOptions. The field ctlPrepTypeResult is populated from a listbox afterupdate.

Private Sub ctlPrepType_AfterUpdate()
On Error GoTo Err_ctlPrepType_AfterUpdate

Dim strWhere As String, varItem As Variant
Dim gstrWhereBook As String
    If Me!ctlPrepType.ItemsSelected.Count = 0 Then
        Me!ctlPrepTypeResult = "*"
        Exit Sub
    End If
    For Each varItem In Me!ctlPrepType.ItemsSelected
        strWhere = strWhere & Chr$(34) & Me!ctlPrepType.Column(0, varItem) & Chr$(34) & ","
        
    Next varItem
    strWhere = Left$(strWhere, Len(strWhere) - 1)
     gstrWhereBook = "In (" & Trim(strWhere) & ")"
    
    If IsNull(strWhere) Then gstrWhereBook = "*"
    Me!ctlPrepTypeResult = Trim(gstrWhereBook)

    Me!ctlPrepTypeResult.Requery
    
Exit_ctlPrepType_AfterUpdate:
    Exit Sub

Err_ctlPrepType_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_ctlPrepType_AfterUpdate
    

End Sub

The query works if I manually populate it. Help !!

 

[dhookom]

You can't use multiple values from an expression like that. Also, if you want to use "*", you must use like (unless your field contains an asterisk).

There is a generic function in the FAQs faq701-6099 that provides a possible solution.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[SmokeEater]

If the unbound control on my form has a string

In ("Power Point","Schematic Drawing")

in it, why can it not be used in the criteria of the query?

 

[dhookom]

You can't send the operator (In) into the query from the form control. The query also can't parse the multiple values. For instance if your query has a where clause like:

[tt][green]WHERE Type_Of_Prep=[Forms]![frmReportOptions]![ctlPrepTypeResult];[/green][/tt]
or
[tt][green]WHERE Type_Of_Prep IN ([Forms]![frmReportOptions]![ctlPrepTypeResult]);[/green][/tt]
or
[tt][green]WHERE Type_Of_Prep Like [Forms]![frmReportOptions]![ctlPrepTypeResult];[/green][/tt]

The only method allowed would be a single value in the control on the form.

You do have some options as I suggested earlier. Did you try it? There are other solutions that modify the SQL property of your saved query.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[SmokeEater]

Thanks for the help. Everything appears to be working now. I have used 'Like' a lot but I guess I have never run into this scenereo before.