Multi-select drop-down for a query (Access 2007)

  • Thread starter Thread starter JimP
  • Start date Start date
J

JimP

I have report based upon a query that uses criteria selected from a combo box
drop-down of an unbound form. This works great but know I have a need to
allow multiple selections for the criteria sort of an "And/Or" situation.

Can it be done? Where do I start?

Thanks
 
Jim,

Don't know what your query looks like now, but I'll assume that you have a
criteria in the Query that uses a syntax similar to:

SELECT Field1, field2, field3
FROM yourTable
WHERE field3 = forms!formname.comboName

What I usually do when I want the user to be able to make multiple
selections is change the query to drop the WHERE clause (or at least that
portion that refers to the combo box).

I then use a listbox instead of a combo box, and pass the OpenReport method
a Where clause that I develop in code associated with a command button, which
might look like:

Private Sub cmd_Preview

dim varItem as Variant
dim strCriteria as string

'Instead of this, you could assume that if no items are
'selected, you want the entire report
if me.lst_YourListName.ItemsSelected.Count = 0 then
msgbox "Select an item from the list"
exit sub
endif

'This assumes the field you want to use to limit the result set is numeric
'and is the first column in the list.
For each varItem in me.lst_YourListName.ItemsSelected
strCriteria = "," & me.lst_YourListName.column(0, varItem)
Next

'strip off the leading character
strCriteria = mid(strCriteria, 2)

'Determine how many values in the list
Select Case Len(strCriteria) - Replace(strCriteria, ",", "")
Case 1
strCriteria = "[FieldName] = " & strCriteria
Case Else
strCriteria = "[FieldName] IN (" & strCriteria & ")"
End Select

docmd.OpenReport "reportname", acViewPreview,,strCriteria

End Sub

If the field you want to evaluate to limit the result set for your report is
a string, then you would need to wrap the value from your list in quotes, in
addition to adding the comma. It would look something like:

strCriteria = ",""" & me.lst_YourListName.column(0, varItem) & """"
 
Dale,

Thanks for the quick reply. I understand most everything you state here but
I know just enough to be dangerous.

Where should the I put this code? Should it be an event activated by a
control button on the list box form, or should be part of the query?

Also if I just want a no selection to return the entire report should I just
not use the If code suggested below?

Thanks again.
JimP

Dale Fye said:
Jim,

Don't know what your query looks like now, but I'll assume that you have a
criteria in the Query that uses a syntax similar to:

SELECT Field1, field2, field3
FROM yourTable
WHERE field3 = forms!formname.comboName

What I usually do when I want the user to be able to make multiple
selections is change the query to drop the WHERE clause (or at least that
portion that refers to the combo box).

I then use a listbox instead of a combo box, and pass the OpenReport method
a Where clause that I develop in code associated with a command button, which
might look like:

Private Sub cmd_Preview

dim varItem as Variant
dim strCriteria as string

'Instead of this, you could assume that if no items are
'selected, you want the entire report
if me.lst_YourListName.ItemsSelected.Count = 0 then
msgbox "Select an item from the list"
exit sub
endif

'This assumes the field you want to use to limit the result set is numeric
'and is the first column in the list.
For each varItem in me.lst_YourListName.ItemsSelected
strCriteria = "," & me.lst_YourListName.column(0, varItem)
Next

'strip off the leading character
strCriteria = mid(strCriteria, 2)

'Determine how many values in the list
Select Case Len(strCriteria) - Replace(strCriteria, ",", "")
Case 1
strCriteria = "[FieldName] = " & strCriteria
Case Else
strCriteria = "[FieldName] IN (" & strCriteria & ")"
End Select

docmd.OpenReport "reportname", acViewPreview,,strCriteria

End Sub

If the field you want to evaluate to limit the result set for your report is
a string, then you would need to wrap the value from your list in quotes, in
addition to adding the comma. It would look something like:

strCriteria = ",""" & me.lst_YourListName.column(0, varItem) & """"


----
HTH
Dale



JimP said:
I have report based upon a query that uses criteria selected from a combo box
drop-down of an unbound form. This works great but know I have a need to
allow multiple selections for the criteria sort of an "And/Or" situation.

Can it be done? Where do I start?

Thanks
Click to expand...
Click to expand...
 
Jim,

Typically, you would have a command button on your form which would allow
you to preview your report. Select that command button, then select the
Click Event associated with that button in the properties dialog. Then
select "Event Procedure" from the drop down in that event, and then click the
"...". This will take you to the code for that event.

Then cut the code between the Private and End Sub lines in the following
code example. I've modified it from the original so that if none of the
items are selected, it will report on all of them.

Private Sub cmd_Preview

dim varItem as Variant
dim strCriteria as string

if me.lst_YourListName.ItemsSelected.Count = 0 then
strCriteria = ""
else
'This assumes the field you want to use to limit the result set is
numeric
'and is the first column in the list.
For each varItem in me.lst_YourListName.ItemsSelected
strCriteria = "," & me.lst_YourListName.column(0, varItem)
Next

'strip off the leading character
strCriteria = mid(strCriteria, 2)

'Determine how many values in the list
Select Case Len(strCriteria) - Replace(strCriteria, ",", "")
Case 1
strCriteria = "[FieldName] = " & strCriteria
Case Else
strCriteria = "[FieldName] IN (" & strCriteria & ")"
End Select
End if

docmd.OpenReport "reportname", acViewPreview,,strCriteria

End Sub

----
HTH
Dale



JimP said:
Dale,

Thanks for the quick reply. I understand most everything you state here but
I know just enough to be dangerous.

Where should the I put this code? Should it be an event activated by a
control button on the list box form, or should be part of the query?

Also if I just want a no selection to return the entire report should I just
not use the If code suggested below?

Thanks again.
JimP

Dale Fye said:
Jim,

Don't know what your query looks like now, but I'll assume that you have a
criteria in the Query that uses a syntax similar to:

SELECT Field1, field2, field3
FROM yourTable
WHERE field3 = forms!formname.comboName

What I usually do when I want the user to be able to make multiple
selections is change the query to drop the WHERE clause (or at least that
portion that refers to the combo box).

I then use a listbox instead of a combo box, and pass the OpenReport method
a Where clause that I develop in code associated with a command button, which
might look like:

Private Sub cmd_Preview

dim varItem as Variant
dim strCriteria as string

'Instead of this, you could assume that if no items are
'selected, you want the entire report
if me.lst_YourListName.ItemsSelected.Count = 0 then
msgbox "Select an item from the list"
exit sub
endif

'This assumes the field you want to use to limit the result set is numeric
'and is the first column in the list.
For each varItem in me.lst_YourListName.ItemsSelected
strCriteria = "," & me.lst_YourListName.column(0, varItem)
Next

'strip off the leading character
strCriteria = mid(strCriteria, 2)

'Determine how many values in the list
Select Case Len(strCriteria) - Replace(strCriteria, ",", "")
Case 1
strCriteria = "[FieldName] = " & strCriteria
Case Else
strCriteria = "[FieldName] IN (" & strCriteria & ")"
End Select

docmd.OpenReport "reportname", acViewPreview,,strCriteria

End Sub

If the field you want to evaluate to limit the result set for your report is
a string, then you would need to wrap the value from your list in quotes, in
addition to adding the comma. It would look something like:

strCriteria = ",""" & me.lst_YourListName.column(0, varItem) & """"


----
HTH
Dale



JimP said:
I have report based upon a query that uses criteria selected from a combo box
drop-down of an unbound form. This works great but know I have a need to
allow multiple selections for the criteria sort of an "And/Or" situation.

Can it be done? Where do I start?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Dale,

Thanks, but when the code attempts to run with any items selected it returns
an error "run time error '13': type mismatch" Debug shows it breaks at

Select Case Len(strCriteria) - Replace(strCriteria, ",", "")

This error does not occrur when no selections are made from the list.

any ideas?

Jim P


Dale Fye said:
Jim,

Typically, you would have a command button on your form which would allow
you to preview your report. Select that command button, then select the
Click Event associated with that button in the properties dialog. Then
select "Event Procedure" from the drop down in that event, and then click the
"...". This will take you to the code for that event.

Then cut the code between the Private and End Sub lines in the following
code example. I've modified it from the original so that if none of the
items are selected, it will report on all of them.

Private Sub cmd_Preview

dim varItem as Variant
dim strCriteria as string

if me.lst_YourListName.ItemsSelected.Count = 0 then
strCriteria = ""
else
'This assumes the field you want to use to limit the result set is
numeric
'and is the first column in the list.
For each varItem in me.lst_YourListName.ItemsSelected
strCriteria = "," & me.lst_YourListName.column(0, varItem)
Next

'strip off the leading character
strCriteria = mid(strCriteria, 2)

'Determine how many values in the list
Select Case Len(strCriteria) - Replace(strCriteria, ",", "")
Case 1
strCriteria = "[FieldName] = " & strCriteria
Case Else
strCriteria = "[FieldName] IN (" & strCriteria & ")"
End Select
End if

docmd.OpenReport "reportname", acViewPreview,,strCriteria

End Sub

----
HTH
Dale



JimP said:
Dale,

Thanks for the quick reply. I understand most everything you state here but
I know just enough to be dangerous.

Where should the I put this code? Should it be an event activated by a
control button on the list box form, or should be part of the query?

Also if I just want a no selection to return the entire report should I just
not use the If code suggested below?

Thanks again.
JimP

Dale Fye said:
Jim,

Don't know what your query looks like now, but I'll assume that you have a
criteria in the Query that uses a syntax similar to:

SELECT Field1, field2, field3
FROM yourTable
WHERE field3 = forms!formname.comboName

What I usually do when I want the user to be able to make multiple
selections is change the query to drop the WHERE clause (or at least that
portion that refers to the combo box).

I then use a listbox instead of a combo box, and pass the OpenReport method
a Where clause that I develop in code associated with a command button, which
might look like:

Private Sub cmd_Preview

dim varItem as Variant
dim strCriteria as string

'Instead of this, you could assume that if no items are
'selected, you want the entire report
if me.lst_YourListName.ItemsSelected.Count = 0 then
msgbox "Select an item from the list"
exit sub
endif

'This assumes the field you want to use to limit the result set is numeric
'and is the first column in the list.
For each varItem in me.lst_YourListName.ItemsSelected
strCriteria = "," & me.lst_YourListName.column(0, varItem)
Next

'strip off the leading character
strCriteria = mid(strCriteria, 2)

'Determine how many values in the list
Select Case Len(strCriteria) - Replace(strCriteria, ",", "")
Case 1
strCriteria = "[FieldName] = " & strCriteria
Case Else
strCriteria = "[FieldName] IN (" & strCriteria & ")"
End Select

docmd.OpenReport "reportname", acViewPreview,,strCriteria

End Sub

If the field you want to evaluate to limit the result set for your report is
a string, then you would need to wrap the value from your list in quotes, in
addition to adding the comma. It would look something like:

strCriteria = ",""" & me.lst_YourListName.column(0, varItem) & """"


----
HTH
Dale



:

I have report based upon a query that uses criteria selected from a combo box
drop-down of an unbound form. This works great but know I have a need to
allow multiple selections for the criteria sort of an "And/Or" situation.

Can it be done? Where do I start?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Jim,

OOPs,

Should be:

Select Case Len(strCriteria) - Len(Replace(strCriteria, ",", ""))


----
HTH
Dale



JimP said:
Dale,

Thanks, but when the code attempts to run with any items selected it returns
an error "run time error '13': type mismatch" Debug shows it breaks at

Select Case Len(strCriteria) - Replace(strCriteria, ",", "")

This error does not occrur when no selections are made from the list.

any ideas?

Jim P


Dale Fye said:
Jim,

Typically, you would have a command button on your form which would allow
you to preview your report. Select that command button, then select the
Click Event associated with that button in the properties dialog. Then
select "Event Procedure" from the drop down in that event, and then click the
"...". This will take you to the code for that event.

Then cut the code between the Private and End Sub lines in the following
code example. I've modified it from the original so that if none of the
items are selected, it will report on all of them.

Private Sub cmd_Preview

dim varItem as Variant
dim strCriteria as string

if me.lst_YourListName.ItemsSelected.Count = 0 then
strCriteria = ""
else
'This assumes the field you want to use to limit the result set is
numeric
'and is the first column in the list.
For each varItem in me.lst_YourListName.ItemsSelected
strCriteria = "," & me.lst_YourListName.column(0, varItem)
Next

'strip off the leading character
strCriteria = mid(strCriteria, 2)

'Determine how many values in the list
Select Case Len(strCriteria) - Replace(strCriteria, ",", "")
Case 1
strCriteria = "[FieldName] = " & strCriteria
Case Else
strCriteria = "[FieldName] IN (" & strCriteria & ")"
End Select
End if

docmd.OpenReport "reportname", acViewPreview,,strCriteria

End Sub

----
HTH
Dale



JimP said:
Dale,

Thanks for the quick reply. I understand most everything you state here but
I know just enough to be dangerous.

Where should the I put this code? Should it be an event activated by a
control button on the list box form, or should be part of the query?

Also if I just want a no selection to return the entire report should I just
not use the If code suggested below?

Thanks again.
JimP

:

Jim,

Don't know what your query looks like now, but I'll assume that you have a
criteria in the Query that uses a syntax similar to:

SELECT Field1, field2, field3
FROM yourTable
WHERE field3 = forms!formname.comboName

What I usually do when I want the user to be able to make multiple
selections is change the query to drop the WHERE clause (or at least that
portion that refers to the combo box).

I then use a listbox instead of a combo box, and pass the OpenReport method
a Where clause that I develop in code associated with a command button, which
might look like:

Private Sub cmd_Preview

dim varItem as Variant
dim strCriteria as string

'Instead of this, you could assume that if no items are
'selected, you want the entire report
if me.lst_YourListName.ItemsSelected.Count = 0 then
msgbox "Select an item from the list"
exit sub
endif

'This assumes the field you want to use to limit the result set is numeric
'and is the first column in the list.
For each varItem in me.lst_YourListName.ItemsSelected
strCriteria = "," & me.lst_YourListName.column(0, varItem)
Next

'strip off the leading character
strCriteria = mid(strCriteria, 2)

'Determine how many values in the list
Select Case Len(strCriteria) - Replace(strCriteria, ",", "")
Case 1
strCriteria = "[FieldName] = " & strCriteria
Case Else
strCriteria = "[FieldName] IN (" & strCriteria & ")"
End Select

docmd.OpenReport "reportname", acViewPreview,,strCriteria

End Sub

If the field you want to evaluate to limit the result set for your report is
a string, then you would need to wrap the value from your list in quotes, in
addition to adding the comma. It would look something like:

strCriteria = ",""" & me.lst_YourListName.column(0, varItem) & """"


----
HTH
Dale



:

I have report based upon a query that uses criteria selected from a combo box
drop-down of an unbound form. This works great but know I have a need to
allow multiple selections for the criteria sort of an "And/Or" situation.

Can it be done? Where do I start?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Dale,

Thanks, I should have caught that simple required parenthasis. Everything
is working now but there is still a slight problem. The query report only
returns the items for the last selection made on the list. What can I do to
make the Query Report return all Items selected. I think it is probably just
a simple code adjustment but not sure what?

I do appreciate all your help. It has been great!

JimP

Dale Fye said:
Jim,

OOPs,

Should be:

Select Case Len(strCriteria) - Len(Replace(strCriteria, ",", ""))


----
HTH
Dale



JimP said:
Dale,

Thanks, but when the code attempts to run with any items selected it returns
an error "run time error '13': type mismatch" Debug shows it breaks at

Select Case Len(strCriteria) - Replace(strCriteria, ",", "")

This error does not occrur when no selections are made from the list.

any ideas?

Jim P


Dale Fye said:
Jim,

Typically, you would have a command button on your form which would allow
you to preview your report. Select that command button, then select the
Click Event associated with that button in the properties dialog. Then
select "Event Procedure" from the drop down in that event, and then click the
"...". This will take you to the code for that event.

Then cut the code between the Private and End Sub lines in the following
code example. I've modified it from the original so that if none of the
items are selected, it will report on all of them.

Private Sub cmd_Preview

dim varItem as Variant
dim strCriteria as string

if me.lst_YourListName.ItemsSelected.Count = 0 then
strCriteria = ""
else
'This assumes the field you want to use to limit the result set is
numeric
'and is the first column in the list.
For each varItem in me.lst_YourListName.ItemsSelected
strCriteria = "," & me.lst_YourListName.column(0, varItem)
Next

'strip off the leading character
strCriteria = mid(strCriteria, 2)

'Determine how many values in the list
Select Case Len(strCriteria) - Replace(strCriteria, ",", "")
Case 1
strCriteria = "[FieldName] = " & strCriteria
Case Else
strCriteria = "[FieldName] IN (" & strCriteria & ")"
End Select
End if

docmd.OpenReport "reportname", acViewPreview,,strCriteria

End Sub

----
HTH
Dale



:

Dale,

Thanks for the quick reply. I understand most everything you state here but
I know just enough to be dangerous.

Where should the I put this code? Should it be an event activated by a
control button on the list box form, or should be part of the query?

Also if I just want a no selection to return the entire report should I just
not use the If code suggested below?

Thanks again.
JimP

:

Jim,

Don't know what your query looks like now, but I'll assume that you have a
criteria in the Query that uses a syntax similar to:

SELECT Field1, field2, field3
FROM yourTable
WHERE field3 = forms!formname.comboName

What I usually do when I want the user to be able to make multiple
selections is change the query to drop the WHERE clause (or at least that
portion that refers to the combo box).

I then use a listbox instead of a combo box, and pass the OpenReport method
a Where clause that I develop in code associated with a command button, which
might look like:

Private Sub cmd_Preview

dim varItem as Variant
dim strCriteria as string

'Instead of this, you could assume that if no items are
'selected, you want the entire report
if me.lst_YourListName.ItemsSelected.Count = 0 then
msgbox "Select an item from the list"
exit sub
endif

'This assumes the field you want to use to limit the result set is numeric
'and is the first column in the list.
For each varItem in me.lst_YourListName.ItemsSelected
strCriteria = "," & me.lst_YourListName.column(0, varItem)
Next

'strip off the leading character
strCriteria = mid(strCriteria, 2)

'Determine how many values in the list
Select Case Len(strCriteria) - Replace(strCriteria, ",", "")
Case 1
strCriteria = "[FieldName] = " & strCriteria
Case Else
strCriteria = "[FieldName] IN (" & strCriteria & ")"
End Select

docmd.OpenReport "reportname", acViewPreview,,strCriteria

End Sub

If the field you want to evaluate to limit the result set for your report is
a string, then you would need to wrap the value from your list in quotes, in
addition to adding the comma. It would look something like:

strCriteria = ",""" & me.lst_YourListName.column(0, varItem) & """"


----
HTH
Dale



:

I have report based upon a query that uses criteria selected from a combo box
drop-down of an unbound form. This works great but know I have a need to
allow multiple selections for the criteria sort of an "And/Or" situation.

Can it be done? Where do I start?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
OOPs again.

Don't know what I'm doing lately.

Inside the For/Next loop, it should read:

strCriteria = strCriteria & "," & me.lst_YourListName.column(0, varItem)


----
HTH
Dale



JimP said:
Dale,

Thanks, I should have caught that simple required parenthasis. Everything
is working now but there is still a slight problem. The query report only
returns the items for the last selection made on the list. What can I do to
make the Query Report return all Items selected. I think it is probably just
a simple code adjustment but not sure what?

I do appreciate all your help. It has been great!

JimP

Dale Fye said:
Jim,

OOPs,

Should be:

Select Case Len(strCriteria) - Len(Replace(strCriteria, ",", ""))


----
HTH
Dale



JimP said:
Dale,

Thanks, but when the code attempts to run with any items selected it returns
an error "run time error '13': type mismatch" Debug shows it breaks at

Select Case Len(strCriteria) - Replace(strCriteria, ",", "")

This error does not occrur when no selections are made from the list.

any ideas?

Jim P


:

Jim,

Typically, you would have a command button on your form which would allow
you to preview your report. Select that command button, then select the
Click Event associated with that button in the properties dialog. Then
select "Event Procedure" from the drop down in that event, and then click the
"...". This will take you to the code for that event.

Then cut the code between the Private and End Sub lines in the following
code example. I've modified it from the original so that if none of the
items are selected, it will report on all of them.

Private Sub cmd_Preview

dim varItem as Variant
dim strCriteria as string

if me.lst_YourListName.ItemsSelected.Count = 0 then
strCriteria = ""
else
'This assumes the field you want to use to limit the result set is
numeric
'and is the first column in the list.
For each varItem in me.lst_YourListName.ItemsSelected
strCriteria = "," & me.lst_YourListName.column(0, varItem)
Next

'strip off the leading character
strCriteria = mid(strCriteria, 2)

'Determine how many values in the list
Select Case Len(strCriteria) - Replace(strCriteria, ",", "")
Case 1
strCriteria = "[FieldName] = " & strCriteria
Case Else
strCriteria = "[FieldName] IN (" & strCriteria & ")"
End Select
End if

docmd.OpenReport "reportname", acViewPreview,,strCriteria

End Sub

----
HTH
Dale



:

Dale,

Thanks for the quick reply. I understand most everything you state here but
I know just enough to be dangerous.

Where should the I put this code? Should it be an event activated by a
control button on the list box form, or should be part of the query?

Also if I just want a no selection to return the entire report should I just
not use the If code suggested below?

Thanks again.
JimP

:

Jim,

Don't know what your query looks like now, but I'll assume that you have a
criteria in the Query that uses a syntax similar to:

SELECT Field1, field2, field3
FROM yourTable
WHERE field3 = forms!formname.comboName

What I usually do when I want the user to be able to make multiple
selections is change the query to drop the WHERE clause (or at least that
portion that refers to the combo box).

I then use a listbox instead of a combo box, and pass the OpenReport method
a Where clause that I develop in code associated with a command button, which
might look like:

Private Sub cmd_Preview

dim varItem as Variant
dim strCriteria as string

'Instead of this, you could assume that if no items are
'selected, you want the entire report
if me.lst_YourListName.ItemsSelected.Count = 0 then
msgbox "Select an item from the list"
exit sub
endif

'This assumes the field you want to use to limit the result set is numeric
'and is the first column in the list.
For each varItem in me.lst_YourListName.ItemsSelected
strCriteria = "," & me.lst_YourListName.column(0, varItem)
Next

'strip off the leading character
strCriteria = mid(strCriteria, 2)

'Determine how many values in the list
Select Case Len(strCriteria) - Replace(strCriteria, ",", "")
Case 1
strCriteria = "[FieldName] = " & strCriteria
Case Else
strCriteria = "[FieldName] IN (" & strCriteria & ")"
End Select

docmd.OpenReport "reportname", acViewPreview,,strCriteria

End Sub

If the field you want to evaluate to limit the result set for your report is
a string, then you would need to wrap the value from your list in quotes, in
addition to adding the comma. It would look something like:

strCriteria = ",""" & me.lst_YourListName.column(0, varItem) & """"


----
HTH
Dale



:

I have report based upon a query that uses criteria selected from a combo box
drop-down of an unbound form. This works great but know I have a need to
allow multiple selections for the criteria sort of an "And/Or" situation.

Can it be done? Where do I start?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Dale,

Thanks again. I had figured out the same thing when you replied. Now
everything works as long great except when I only make two selections from
the list. For some reason when I select only two items from the list it
returns a "Run-toime error '3075' Syntax error (comma) in query expression
'[qyrMDSfilter.MDS]="HC-130N","HC-130P"'.

Any Ideas?

I promiss this will be my last post.

Jim

Dale Fye said:
OOPs again.

Don't know what I'm doing lately.

Inside the For/Next loop, it should read:

strCriteria = strCriteria & "," & me.lst_YourListName.column(0, varItem)


----
HTH
Dale



JimP said:
Dale,

Thanks, I should have caught that simple required parenthasis. Everything
is working now but there is still a slight problem. The query report only
returns the items for the last selection made on the list. What can I do to
make the Query Report return all Items selected. I think it is probably just
a simple code adjustment but not sure what?

I do appreciate all your help. It has been great!

JimP

Dale Fye said:
Jim,

OOPs,

Should be:

Select Case Len(strCriteria) - Len(Replace(strCriteria, ",", ""))


----
HTH
Dale



:


Dale,

Thanks, but when the code attempts to run with any items selected it returns
an error "run time error '13': type mismatch" Debug shows it breaks at

Select Case Len(strCriteria) - Replace(strCriteria, ",", "")

This error does not occrur when no selections are made from the list.

any ideas?

Jim P


:

Jim,

Typically, you would have a command button on your form which would allow
you to preview your report. Select that command button, then select the
Click Event associated with that button in the properties dialog. Then
select "Event Procedure" from the drop down in that event, and then click the
"...". This will take you to the code for that event.

Then cut the code between the Private and End Sub lines in the following
code example. I've modified it from the original so that if none of the
items are selected, it will report on all of them.

Private Sub cmd_Preview

dim varItem as Variant
dim strCriteria as string

if me.lst_YourListName.ItemsSelected.Count = 0 then
strCriteria = ""
else
'This assumes the field you want to use to limit the result set is
numeric
'and is the first column in the list.
For each varItem in me.lst_YourListName.ItemsSelected
strCriteria = "," & me.lst_YourListName.column(0, varItem)
Next

'strip off the leading character
strCriteria = mid(strCriteria, 2)

'Determine how many values in the list
Select Case Len(strCriteria) - Replace(strCriteria, ",", "")
Case 1
strCriteria = "[FieldName] = " & strCriteria
Case Else
strCriteria = "[FieldName] IN (" & strCriteria & ")"
End Select
End if

docmd.OpenReport "reportname", acViewPreview,,strCriteria

End Sub

----
HTH
Dale



:

Dale,

Thanks for the quick reply. I understand most everything you state here but
I know just enough to be dangerous.

Where should the I put this code? Should it be an event activated by a
control button on the list box form, or should be part of the query?

Also if I just want a no selection to return the entire report should I just
not use the If code suggested below?

Thanks again.
JimP

:

Jim,

Don't know what your query looks like now, but I'll assume that you have a
criteria in the Query that uses a syntax similar to:

SELECT Field1, field2, field3
FROM yourTable
WHERE field3 = forms!formname.comboName

What I usually do when I want the user to be able to make multiple
selections is change the query to drop the WHERE clause (or at least that
portion that refers to the combo box).

I then use a listbox instead of a combo box, and pass the OpenReport method
a Where clause that I develop in code associated with a command button, which
might look like:

Private Sub cmd_Preview

dim varItem as Variant
dim strCriteria as string

'Instead of this, you could assume that if no items are
'selected, you want the entire report
if me.lst_YourListName.ItemsSelected.Count = 0 then
msgbox "Select an item from the list"
exit sub
endif

'This assumes the field you want to use to limit the result set is numeric
'and is the first column in the list.
For each varItem in me.lst_YourListName.ItemsSelected
strCriteria = "," & me.lst_YourListName.column(0, varItem)
Next

'strip off the leading character
strCriteria = mid(strCriteria, 2)

'Determine how many values in the list
Select Case Len(strCriteria) - Replace(strCriteria, ",", "")
Case 1
strCriteria = "[FieldName] = " & strCriteria
Case Else
strCriteria = "[FieldName] IN (" & strCriteria & ")"
End Select

docmd.OpenReport "reportname", acViewPreview,,strCriteria

End Sub

If the field you want to evaluate to limit the result set for your report is
a string, then you would need to wrap the value from your list in quotes, in
addition to adding the comma. It would look something like:

strCriteria = ",""" & me.lst_YourListName.column(0, varItem) & """"


----
HTH
Dale



:

I have report based upon a query that uses criteria selected from a combo box
drop-down of an unbound form. This works great but know I have a need to
allow multiple selections for the criteria sort of an "And/Or" situation.

Can it be done? Where do I start?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Replace:

Case 1

with

Case 0

basically, what that Select Case statement is supposed to do is define what
the critieria string looks like. If there are no commas, then it should use
an "=" sign, if there is one or more commas, it should use the "IN ("a", "b",
"c" ) clause, so:

[qyrMDSfilter.MDS]="HC-130N","HC-130P"

Should actually read:

[qyrMDSfilter.MDS] IN ("HC-130N","HC-130P")

----
HTH
Dale



JimP said:
Dale,

Thanks again. I had figured out the same thing when you replied. Now
everything works as long great except when I only make two selections from
the list. For some reason when I select only two items from the list it
returns a "Run-toime error '3075' Syntax error (comma) in query expression
'[qyrMDSfilter.MDS]="HC-130N","HC-130P"'.

Any Ideas?

I promiss this will be my last post.

Jim

Dale Fye said:
OOPs again.

Don't know what I'm doing lately.

Inside the For/Next loop, it should read:

strCriteria = strCriteria & "," & me.lst_YourListName.column(0, varItem)


----
HTH
Dale



JimP said:
Dale,

Thanks, I should have caught that simple required parenthasis. Everything
is working now but there is still a slight problem. The query report only
returns the items for the last selection made on the list. What can I do to
make the Query Report return all Items selected. I think it is probably just
a simple code adjustment but not sure what?

I do appreciate all your help. It has been great!

JimP

:

Jim,

OOPs,

Should be:

Select Case Len(strCriteria) - Len(Replace(strCriteria, ",", ""))


----
HTH
Dale



:


Dale,

Thanks, but when the code attempts to run with any items selected it returns
an error "run time error '13': type mismatch" Debug shows it breaks at

Select Case Len(strCriteria) - Replace(strCriteria, ",", "")

This error does not occrur when no selections are made from the list.

any ideas?

Jim P


:

Jim,

Typically, you would have a command button on your form which would allow
you to preview your report. Select that command button, then select the
Click Event associated with that button in the properties dialog. Then
select "Event Procedure" from the drop down in that event, and then click the
"...". This will take you to the code for that event.

Then cut the code between the Private and End Sub lines in the following
code example. I've modified it from the original so that if none of the
items are selected, it will report on all of them.

Private Sub cmd_Preview

dim varItem as Variant
dim strCriteria as string

if me.lst_YourListName.ItemsSelected.Count = 0 then
strCriteria = ""
else
'This assumes the field you want to use to limit the result set is
numeric
'and is the first column in the list.
For each varItem in me.lst_YourListName.ItemsSelected
strCriteria = "," & me.lst_YourListName.column(0, varItem)
Next

'strip off the leading character
strCriteria = mid(strCriteria, 2)

'Determine how many values in the list
Select Case Len(strCriteria) - Replace(strCriteria, ",", "")
Case 1
strCriteria = "[FieldName] = " & strCriteria
Case Else
strCriteria = "[FieldName] IN (" & strCriteria & ")"
End Select
End if

docmd.OpenReport "reportname", acViewPreview,,strCriteria

End Sub

----
HTH
Dale



:

Dale,

Thanks for the quick reply. I understand most everything you state here but
I know just enough to be dangerous.

Where should the I put this code? Should it be an event activated by a
control button on the list box form, or should be part of the query?

Also if I just want a no selection to return the entire report should I just
not use the If code suggested below?

Thanks again.
JimP

:

Jim,

Don't know what your query looks like now, but I'll assume that you have a
criteria in the Query that uses a syntax similar to:

SELECT Field1, field2, field3
FROM yourTable
WHERE field3 = forms!formname.comboName

What I usually do when I want the user to be able to make multiple
selections is change the query to drop the WHERE clause (or at least that
portion that refers to the combo box).

I then use a listbox instead of a combo box, and pass the OpenReport method
a Where clause that I develop in code associated with a command button, which
might look like:

Private Sub cmd_Preview

dim varItem as Variant
dim strCriteria as string

'Instead of this, you could assume that if no items are
'selected, you want the entire report
if me.lst_YourListName.ItemsSelected.Count = 0 then
msgbox "Select an item from the list"
exit sub
endif

'This assumes the field you want to use to limit the result set is numeric
'and is the first column in the list.
For each varItem in me.lst_YourListName.ItemsSelected
strCriteria = "," & me.lst_YourListName.column(0, varItem)
Next

'strip off the leading character
strCriteria = mid(strCriteria, 2)

'Determine how many values in the list
Select Case Len(strCriteria) - Replace(strCriteria, ",", "")
Case 1
strCriteria = "[FieldName] = " & strCriteria
Case Else
strCriteria = "[FieldName] IN (" & strCriteria & ")"
End Select

docmd.OpenReport "reportname", acViewPreview,,strCriteria

End Sub

If the field you want to evaluate to limit the result set for your report is
a string, then you would need to wrap the value from your list in quotes, in
addition to adding the comma. It would look something like:

strCriteria = ",""" & me.lst_YourListName.column(0, varItem) & """"


----
HTH
Dale



:

I have report based upon a query that uses criteria selected from a combo box
drop-down of an unbound form. This works great but know I have a need to
allow multiple selections for the criteria sort of an "And/Or" situation.

Can it be done? Where do I start?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top