list box criteria

[angie]

i have a form that i use to set criteria for my query. i have 3 combo boxes
and an option group with two options. when option 1 is selected i want to
return all the records but when option 2 is selected i want to return the
records selected in my multiselect listbox.

in my query i have set the following criteria in the corresponding fields
based on the selection of my combo boxes.
e.g. iif([forms]![myformname]![comboname] is
null;[fieldname];[forms]![myformname]![comboname]).
this works fine for the three fields.

how can i add one more criteria based on my optiongroup (and multiselect
listbox)?

 

[Klatuu]

To use a multiselect list box, you will need to use some VBA. Here is a
function that will return a string formated your use:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Now, since you will not be able to reference your list box in your query,
you are really going to have to do one of two things, depending on how and
where you are using the query.

In some cases, it may be best to build the query in VBA and execute it from
there, in cases where a stored query is required, you will need to modify
the query's SQL and save it back. If what you are doing is trying to filter
a report or form you are opening, you would use your criteria string in the
Where argument of the OpenForm or Openreport method.

If you can describe exactly what you are doing, I will be happy to help with
the correct way to accomplish your goal.

 

[angie]

actually i have many forms and reports based on that query as well as many
filters applied through the "onopen" event,etc. i would save considerable
time and it is always more clear in my mind if i use the query for this task.
the problem is i have limited knowledge of VBA. could you guide me step by
step to accomplish this task?

Ο χÏήστης "Klatuu" έγγÏαψε:

To use a multiselect list box, you will need to use some VBA. Here is a
function that will return a string formated your use:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Now, since you will not be able to reference your list box in your query,
you are really going to have to do one of two things, depending on how and
where you are using the query.

In some cases, it may be best to build the query in VBA and execute it from
there, in cases where a stored query is required, you will need to modify
the query's SQL and save it back. If what you are doing is trying to filter
a report or form you are opening, you would use your criteria string in the
Where argument of the OpenForm or Openreport method.

If you can describe exactly what you are doing, I will be happy to help with
the correct way to accomplish your goal.

i have a form that i use to set criteria for my query. i have 3 combo boxes
and an option group with two options. when option 1 is selected i want to
return all the records but when option 2 is selected i want to return the
records selected in my multiselect listbox.

in my query i have set the following criteria in the corresponding fields
based on the selection of my combo boxes.
e.g. iif([forms]![myformname]![comboname] is
null;[fieldname];[forms]![myformname]![comboname]).
this works fine for the three fields.

how can i add one more criteria based on my optiongroup (and multiselect
listbox)?

 

[Klatuu]

What is it, exatly, that you want to do?

actually i have many forms and reports based on that query as well as many
filters applied through the "onopen" event,etc. i would save considerable
time and it is always more clear in my mind if i use the query for this
task.
the problem is i have limited knowledge of VBA. could you guide me step by
step to accomplish this task?

? ??????? "Klatuu" ???????:

To use a multiselect list box, you will need to use some VBA. Here is a
function that will return a string formated your use:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "',
"
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Now, since you will not be able to reference your list box in your query,
you are really going to have to do one of two things, depending on how
and
where you are using the query.

In some cases, it may be best to build the query in VBA and execute it
from
there, in cases where a stored query is required, you will need to modify
the query's SQL and save it back. If what you are doing is trying to
filter
a report or form you are opening, you would use your criteria string in
the
Where argument of the OpenForm or Openreport method.

If you can describe exactly what you are doing, I will be happy to help
with
the correct way to accomplish your goal.

i have a form that i use to set criteria for my query. i have 3 combo
boxes
and an option group with two options. when option 1 is selected i want
to
return all the records but when option 2 is selected i want to return
the
records selected in my multiselect listbox.

in my query i have set the following criteria in the corresponding
fields
based on the selection of my combo boxes.
e.g. iif([forms]![myformname]![comboname] is
null;[fieldname];[forms]![myformname]![comboname]).
this works fine for the three fields.

how can i add one more criteria based on my optiongroup (and
multiselect
listbox)?

 

[NHMM]

Hi Klatuu, I didn't follow what you meant when you said:
"If what you are doing is trying to filter a report or form you are opening,
you would use your criteria string in the Where argument of the OpenForm or
Openreport method."

I am trying to use a multiselect listbox as criteria for a querty which will
ultimately be the filter for a report. I have a sense what the code is trying
to accomplish but not exactly where to put everything and tie it all together.
Thanks for the help
NHMM

To use a multiselect list box, you will need to use some VBA. Here is a
function that will return a string formated your use:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Now, since you will not be able to reference your list box in your query,
you are really going to have to do one of two things, depending on how and
where you are using the query.

In some cases, it may be best to build the query in VBA and execute it from
there, in cases where a stored query is required, you will need to modify
the query's SQL and save it back. If what you are doing is trying to filter
a report or form you are opening, you would use your criteria string in the
Where argument of the OpenForm or Openreport method.

If you can describe exactly what you are doing, I will be happy to help with
the correct way to accomplish your goal.

i have a form that i use to set criteria for my query. i have 3 combo boxes
and an option group with two options. when option 1 is selected i want to
return all the records but when option 2 is selected i want to return the
records selected in my multiselect listbox.

in my query i have set the following criteria in the corresponding fields
based on the selection of my combo boxes.
e.g. iif([forms]![myformname]![comboname] is
null;[fieldname];[forms]![myformname]![comboname]).
this works fine for the three fields.

how can i add one more criteria based on my optiongroup (and multiselect
listbox)?