Hi,
I have a form "Reports" that contains list box from which user has an option of multi-select choises which needs to be passed into the Query as the parameter for the were clause condition.
"Where [User Name] in [Forms]![Reports]![lst_Auditor]". The result of this query is the Access report
What is the best way to do this?
I extracted chosen values but not sure now to pass it to the query. From the example below I was passing the result to the hiden textbox on the same form [text_Auditor] and then adding the text box to the query condition [Forms]![Reports]![Text_Auditor]. But I am getting the error "The Microsoft Access database engine does not recognize [Forms]![Reports]![Text_Auditor] as a valid field name or expression. (Error 3070)"
Private Sub cmdApplyFilter_Click()
Dim varItem As Variant
Dim strAuditor As String
Dim strFilterAU As String
' Build criteria string from Auditor listbox
For Each varItem In Me.Auditor.ItemsSelected
strAuditor = strAuditor & ",'" & Me.Auditor.ItemData(varItem) _
& "'"
Next varItem
If Len(strAuditor) = 0 Then
strAuditor = "Like '*'"
Else
strAuditor = Right(strAuditor, Len(strAuditor) - 1)
strAuditor = "IN(" & strAuditor & ")"
'strAuditor = strAuditor
End If
' Build filter string
strFilterAU = strAuditor
With Forms!Reports!Text_CM
.SetFocus
.Text = strFilterCM
End With
I also have date range text boxes on the same for which I was able to pass to the query :
Between DateValue([forms]![reports]![from_date]) And DateValue([forms]![reports]![thru_date])
What am I doing wrong?
I have a form "Reports" that contains list box from which user has an option of multi-select choises which needs to be passed into the Query as the parameter for the were clause condition.
"Where [User Name] in [Forms]![Reports]![lst_Auditor]". The result of this query is the Access report
What is the best way to do this?
I extracted chosen values but not sure now to pass it to the query. From the example below I was passing the result to the hiden textbox on the same form [text_Auditor] and then adding the text box to the query condition [Forms]![Reports]![Text_Auditor]. But I am getting the error "The Microsoft Access database engine does not recognize [Forms]![Reports]![Text_Auditor] as a valid field name or expression. (Error 3070)"
Private Sub cmdApplyFilter_Click()
Dim varItem As Variant
Dim strAuditor As String
Dim strFilterAU As String
' Build criteria string from Auditor listbox
For Each varItem In Me.Auditor.ItemsSelected
strAuditor = strAuditor & ",'" & Me.Auditor.ItemData(varItem) _
& "'"
Next varItem
If Len(strAuditor) = 0 Then
strAuditor = "Like '*'"
Else
strAuditor = Right(strAuditor, Len(strAuditor) - 1)
strAuditor = "IN(" & strAuditor & ")"
'strAuditor = strAuditor
End If
' Build filter string
strFilterAU = strAuditor
With Forms!Reports!Text_CM
.SetFocus
.Text = strFilterCM
End With
I also have date range text boxes on the same for which I was able to pass to the query :
Between DateValue([forms]![reports]![from_date]) And DateValue([forms]![reports]![thru_date])
What am I doing wrong?
