using a function as the criteria for a field

[ChasW]

I just read these 2 pages. These are most helpful, but leave me with
a question.

http://www.mvps.org/access/queries/qry0005.htm
http://www.mvps.org/access/forms/frm0007.htm

I have a Multi Select Listbox that will define query criteria for a
query that is the record source for another form.

Certain individual selections in this list box may act a label for a
group of other selections, so I am using a VBA function to create the
string that will define the WHERE clause of the SQL statement for the
query.

Quoting a portion of the above link: "Note: You can still use a
parameterized query provided you pass the entire Where clause to it
via code as a parameter. (eg. Have the query reference a hidden
control to which you manually assign the complete WHERE clause using
the following logic.)"

Assuming I understand the above quote correctly, I guess what I am
seeking is more or less syntax for assigning a complete WHERE clause
as opposed to a specific field criteria from a control.

Anybody care to give an example of this?

Chas

[Ed Robichaud]

I've tried a bunch, but the method below seems to be the easiest way to
implement multi-pick combo boxes. It's copied from a very generous MVP, but
I've forgotten who.

Column in Query:

IsSelectedVar("frmMyForm","cmbMyMultiPick",[myField])
Function in module:
=========================================
Function IsSelectedVar( _
strFormName As String, _
strListBoxName As String, _
varValue As Variant) _
As Boolean
'strFormName is the name of the form
'strListBoxName is the name of the listbox
'varValue is the field to check against the listbox
Dim lbo As ListBox
Dim item As Variant
If IsNumeric(varValue) Then
varValue = Trim(Str(varValue))
End If
Set lbo = Forms(strFormName)(strListBoxName)
If lbo.ItemsSelected.Count > 0 Then
For Each item In lbo.ItemsSelected
If lbo.ItemData(item) = varValue Then
IsSelectedVar = True
Exit Function
End If
Next
Else
IsSelectedVar = True
End If

End Function
=========================================
<ChasW> wrote in message news:bg********************************@4ax.com...

I just read these 2 pages. These are most helpful, but leave me with
a question.

http://www.mvps.org/access/queries/qry0005.htm
http://www.mvps.org/access/forms/frm0007.htm

I have a Multi Select Listbox that will define query criteria for a
query that is the record source for another form.

Certain individual selections in this list box may act a label for a
group of other selections, so I am using a VBA function to create the
string that will define the WHERE clause of the SQL statement for the
query.

Quoting a portion of the above link: "Note: You can still use a
parameterized query provided you pass the entire Where clause to it
via code as a parameter. (eg. Have the query reference a hidden
control to which you manually assign the complete WHERE clause using
the following logic.)"

Assuming I understand the above quote correctly, I guess what I am
seeking is more or less syntax for assigning a complete WHERE clause
as opposed to a specific field criteria from a control.

Anybody care to give an example of this?

Chas

[Ed Robichaud]

Due credit to Duane Hookom; here is the original source of the solution that
I quoted.

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Listbox%20with%20Mul tiselection%20used%20in%20Query'
"Ed Robichaud" <ed*********@wdn.com> wrote in message
news:44**********************@news.wdn.com...

I've tried a bunch, but the method below seems to be the easiest way to
implement multi-pick combo boxes. It's copied from a very generous MVP,
but I've forgotten who.

Column in Query:

IsSelectedVar("frmMyForm","cmbMyMultiPick",[myField])
Function in module:
=========================================
Function IsSelectedVar( _
strFormName As String, _
strListBoxName As String, _
varValue As Variant) _
As Boolean
'strFormName is the name of the form
'strListBoxName is the name of the listbox
'varValue is the field to check against the listbox
Dim lbo As ListBox
Dim item As Variant
If IsNumeric(varValue) Then
varValue = Trim(Str(varValue))
End If
Set lbo = Forms(strFormName)(strListBoxName)
If lbo.ItemsSelected.Count > 0 Then
For Each item In lbo.ItemsSelected
If lbo.ItemData(item) = varValue Then
IsSelectedVar = True
Exit Function
End If
Next
Else
IsSelectedVar = True
End If

End Function
=========================================
<ChasW> wrote in message
news:bg********************************@4ax.com...

I just read these 2 pages. These are most helpful, but leave me with
a question.

http://www.mvps.org/access/queries/qry0005.htm
http://www.mvps.org/access/forms/frm0007.htm

I have a Multi Select Listbox that will define query criteria for a
query that is the record source for another form.

Certain individual selections in this list box may act a label for a
group of other selections, so I am using a VBA function to create the
string that will define the WHERE clause of the SQL statement for the
query.

Quoting a portion of the above link: "Note: You can still use a
parameterized query provided you pass the entire Where clause to it
via code as a parameter. (eg. Have the query reference a hidden
control to which you manually assign the complete WHERE clause using
the following logic.)"

Assuming I understand the above quote correctly, I guess what I am
seeking is more or less syntax for assigning a complete WHERE clause
as opposed to a specific field criteria from a control.

Anybody care to give an example of this?

Chas