Multiple Select List Box as a Parameter?

[Ashley]

I have created a form that displays when I startup the database called
frmStartupscreen. On that form are 3 command buttons to run a form, report
and chart. There is also a place to select multiple states out of a list box.
the list box is named States. What I would like to do is take the selections
from the States list box and apply it to a query named CPM Query that runs
the report and chart.

Is there a way that I can do that using the criteria box in the design view
of the Query? Or would I need to know VB or SQL? I'll be honest, I don't have
any experience in those two, so any help would be greatly appreciated!

Thanks

 

[Mark Andrews]

Afraid you might have to get into VB.

Try this for help:
http://allenbrowne.com/ser-50.html

Mark
RPT Software
http://www.rptsoftware.com

 

[Ken Sheridan]

To reference the selections form the list box as a parameter you'd
firstly have to build a value list in a hidden text box control so
that the query has something to reference. However, the IN operator
does not accept a parameter as its argument, so you have to simulate
it. You'll find a couple of ways of doing this at:

http://support.microsoft.com/kb/100131/en-us

I'd recommend the second solution, using the InParam function as this
makes building the value list more straightforward as it caters for
any data type in exactly the same way, and you don't have to cater for
the possibility of a value being a substring of another value. So
using that method, if the hidden text box on the form is named
txtStatesHidden say, and the list box is named States the code to fill
the text box, which you'd put in the button's Click event procedure
before code to open the report etc, would be like this to open a
report in print preview for example:

Dim varItem As Variant
Dim strStatesList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.States

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStatesList = strStatesList & "," & ctrl.ItemData
(varItem)
Next varItem

' remove leading comma
strStatesList = Mid(strStatesList, 2)

' assign value list to hidden text box
Me.txtStatesHidden = strStatesList

DoCmd.OpenReport "YourReport", _
View:=acViewPreview
Else
MsgBox "No States Selected", vbInformation, "Warning"
End If

The query would be restricted as follows:

WHERE InParam(State, Forms!YourForm!txtStatesHidden)

where State is the name of the column in the table. In query design
view you'd enter InParam([State], Forms!YourForm!txtStatesHidden) into
the 'field' row of a blank column in the design grid, uncheck the
'show' check box, and in the column's 'criteria' row you'd enter True.

An alternative approach would be not to restrict the query at all, so
you don't need the hidden text box or the InParam function, but to
build a string expression for the WhereCondtion of the OpenReport
method:

Dim varItem As Variant
Dim strStatesList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.States

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStatesList = strStatesList & ",""" & ctrl.ItemData
(varItem) & """"
Next varItem

' remove leading comma
strStatesList = Mid(strStatesList, 2)

strCriteria = "State In(" & strStatesList & ")"

DoCmd.OpenReport "YourReport", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No States Selected", vbInformation, "Warning"
End If

This assumes that the State column in the table is a text data type
rather than a 'surrogate' numeric ID value.

Ken Sheridan
Stafford, England

 

[Ashley]

Ken-

I tried the code that you provided. I seem to have run into a problem
though. When I try and run the query it gives me an error message that says
"Undefined function'InParam' in Expression."

Here is a copy of my SQL code for my Query.

SELECT CPM.[Ship Week], CPM.[Destination State], CPM.[Avg CPM], CPM.[Avg
RPM], [Weekly Fuel Rates].[Fuel Price]
FROM [Weekly Fuel Rates] INNER JOIN CPM ON [Weekly Fuel
Rates].Week=CPM.[Ship Week]
WHERE (((CPM.[Ship Week]) Between (Forms!frmStartupscreen!StartDate) And
(Forms!frmStartupscreen!EndDate)) And
((InParam([DestinationState],Forms!frmStartupscreen!txtStatesHidden))=True));

Perhaps my code is wrong? I'm still pretty new at this and not exactly sure
what I am looking at.

Any ideas what I could be doing wrong?

Thanks

To reference the selections form the list box as a parameter you'd
firstly have to build a value list in a hidden text box control so
that the query has something to reference. However, the IN operator
does not accept a parameter as its argument, so you have to simulate
it. You'll find a couple of ways of doing this at:

http://support.microsoft.com/kb/100131/en-us

I'd recommend the second solution, using the InParam function as this
makes building the value list more straightforward as it caters for
any data type in exactly the same way, and you don't have to cater for
the possibility of a value being a substring of another value. So
using that method, if the hidden text box on the form is named
txtStatesHidden say, and the list box is named States the code to fill
the text box, which you'd put in the button's Click event procedure
before code to open the report etc, would be like this to open a
report in print preview for example:

Dim varItem As Variant
Dim strStatesList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.States

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStatesList = strStatesList & "," & ctrl.ItemData
(varItem)
Next varItem

' remove leading comma
strStatesList = Mid(strStatesList, 2)

' assign value list to hidden text box
Me.txtStatesHidden = strStatesList

DoCmd.OpenReport "YourReport", _
View:=acViewPreview
Else
MsgBox "No States Selected", vbInformation, "Warning"
End If

The query would be restricted as follows:

WHERE InParam(State, Forms!YourForm!txtStatesHidden)

where State is the name of the column in the table. In query design
view you'd enter InParam([State], Forms!YourForm!txtStatesHidden) into
the 'field' row of a blank column in the design grid, uncheck the
'show' check box, and in the column's 'criteria' row you'd enter True.

An alternative approach would be not to restrict the query at all, so
you don't need the hidden text box or the InParam function, but to
build a string expression for the WhereCondtion of the OpenReport
method:

Dim varItem As Variant
Dim strStatesList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.States

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStatesList = strStatesList & ",""" & ctrl.ItemData
(varItem) & """"
Next varItem

' remove leading comma
strStatesList = Mid(strStatesList, 2)

strCriteria = "State In(" & strStatesList & ")"

DoCmd.OpenReport "YourReport", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No States Selected", vbInformation, "Warning"
End If

This assumes that the State column in the table is a text data type
rather than a 'surrogate' numeric ID value.

Ken Sheridan
Stafford, England

I have created a form that displays when I startup the database called
frmStartupscreen. On that form are 3 command buttons to run a form, report
and chart. There is also a place to select multiple states out of a list box.
the list box is named States. What I would like to do is take the selections
from the States list box and apply it to a query named CPM Query that runs
the report and chart.

Is there a way that I can do that using the criteria box in the design view
of the Query? Or would I need to know VB or SQL? I'll be honest, I don't have
any experience in those two, so any help would be greatly appreciated!

Thanks

 

[Ashley]

I also just saw that when I enter the code below I get an error that says
"Compile error: Argument not Optional"

Here's the code that is attached to my button to run the form that I am
trying to make work.
Private Sub btnChart1_Click()

Dim varItem As Variant
Dim strStatesList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.States

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStatesList = strStatesList & "," & ctrl.ItemData

Next varItem

' remove leading comma
strStatesList = Mid(strStatesList, 2)

' assign value list to hidden text box
Me.txtStatesHidden = strStatesList

DoCmd.OpenForm "Cost/Rate Per Mile", _
View:=acViewPreview
Else
MsgBox "No States Selected", vbInformation, "Warning"
End If
End Sub

Did I do something wrong?

Thanks for your help

To reference the selections form the list box as a parameter you'd
firstly have to build a value list in a hidden text box control so
that the query has something to reference. However, the IN operator
does not accept a parameter as its argument, so you have to simulate
it. You'll find a couple of ways of doing this at:

http://support.microsoft.com/kb/100131/en-us

I'd recommend the second solution, using the InParam function as this
makes building the value list more straightforward as it caters for
any data type in exactly the same way, and you don't have to cater for
the possibility of a value being a substring of another value. So
using that method, if the hidden text box on the form is named
txtStatesHidden say, and the list box is named States the code to fill
the text box, which you'd put in the button's Click event procedure
before code to open the report etc, would be like this to open a
report in print preview for example:

Dim varItem As Variant
Dim strStatesList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.States

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStatesList = strStatesList & "," & ctrl.ItemData
(varItem)
Next varItem

' remove leading comma
strStatesList = Mid(strStatesList, 2)

' assign value list to hidden text box
Me.txtStatesHidden = strStatesList

DoCmd.OpenReport "YourReport", _
View:=acViewPreview
Else
MsgBox "No States Selected", vbInformation, "Warning"
End If

The query would be restricted as follows:

WHERE InParam(State, Forms!YourForm!txtStatesHidden)

where State is the name of the column in the table. In query design
view you'd enter InParam([State], Forms!YourForm!txtStatesHidden) into
the 'field' row of a blank column in the design grid, uncheck the
'show' check box, and in the column's 'criteria' row you'd enter True.

An alternative approach would be not to restrict the query at all, so
you don't need the hidden text box or the InParam function, but to
build a string expression for the WhereCondtion of the OpenReport
method:

Dim varItem As Variant
Dim strStatesList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.States

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStatesList = strStatesList & ",""" & ctrl.ItemData
(varItem) & """"
Next varItem

' remove leading comma
strStatesList = Mid(strStatesList, 2)

strCriteria = "State In(" & strStatesList & ")"

DoCmd.OpenReport "YourReport", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No States Selected", vbInformation, "Warning"
End If

This assumes that the State column in the table is a text data type
rather than a 'surrogate' numeric ID value.

Ken Sheridan
Stafford, England

I have created a form that displays when I startup the database called
frmStartupscreen. On that form are 3 command buttons to run a form, report
and chart. There is also a place to select multiple states out of a list box.
the list box is named States. What I would like to do is take the selections
from the States list box and apply it to a query named CPM Query that runs
the report and chart.

Is there a way that I can do that using the criteria box in the design view
of the Query? Or would I need to know VB or SQL? I'll be honest, I don't have
any experience in those two, so any help would be greatly appreciated!

Thanks

 

[Douglas J. Steele]

It needs to be

ctrl.ItemData(varItem)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I also just saw that when I enter the code below I get an error that says
"Compile error: Argument not Optional"

Here's the code that is attached to my button to run the form that I am
trying to make work.
Private Sub btnChart1_Click()

Dim varItem As Variant
Dim strStatesList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.States

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStatesList = strStatesList & "," & ctrl.ItemData

Next varItem

' remove leading comma
strStatesList = Mid(strStatesList, 2)

' assign value list to hidden text box
Me.txtStatesHidden = strStatesList

DoCmd.OpenForm "Cost/Rate Per Mile", _
View:=acViewPreview
Else
MsgBox "No States Selected", vbInformation, "Warning"
End If
End Sub

Did I do something wrong?

Thanks for your help

To reference the selections form the list box as a parameter you'd
firstly have to build a value list in a hidden text box control so
that the query has something to reference. However, the IN operator
does not accept a parameter as its argument, so you have to simulate
it. You'll find a couple of ways of doing this at:

http://support.microsoft.com/kb/100131/en-us

I'd recommend the second solution, using the InParam function as this
makes building the value list more straightforward as it caters for
any data type in exactly the same way, and you don't have to cater for
the possibility of a value being a substring of another value. So
using that method, if the hidden text box on the form is named
txtStatesHidden say, and the list box is named States the code to fill
the text box, which you'd put in the button's Click event procedure
before code to open the report etc, would be like this to open a
report in print preview for example:

Dim varItem As Variant
Dim strStatesList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.States

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStatesList = strStatesList & "," & ctrl.ItemData
(varItem)
Next varItem

' remove leading comma
strStatesList = Mid(strStatesList, 2)

' assign value list to hidden text box
Me.txtStatesHidden = strStatesList

DoCmd.OpenReport "YourReport", _
View:=acViewPreview
Else
MsgBox "No States Selected", vbInformation, "Warning"
End If

The query would be restricted as follows:

WHERE InParam(State, Forms!YourForm!txtStatesHidden)

where State is the name of the column in the table. In query design
view you'd enter InParam([State], Forms!YourForm!txtStatesHidden) into
the 'field' row of a blank column in the design grid, uncheck the
'show' check box, and in the column's 'criteria' row you'd enter True.

An alternative approach would be not to restrict the query at all, so
you don't need the hidden text box or the InParam function, but to
build a string expression for the WhereCondtion of the OpenReport
method:

Dim varItem As Variant
Dim strStatesList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.States

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStatesList = strStatesList & ",""" & ctrl.ItemData
(varItem) & """"
Next varItem

' remove leading comma
strStatesList = Mid(strStatesList, 2)

strCriteria = "State In(" & strStatesList & ")"

DoCmd.OpenReport "YourReport", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No States Selected", vbInformation, "Warning"
End If

This assumes that the State column in the table is a text data type
rather than a 'surrogate' numeric ID value.

Ken Sheridan
Stafford, England

I have created a form that displays when I startup the database called
frmStartupscreen. On that form are 3 command buttons to run a form,
report
and chart. There is also a place to select multiple states out of a
list box.
the list box is named States. What I would like to do is take the
selections
from the States list box and apply it to a query named CPM Query that
runs
the report and chart.

Is there a way that I can do that using the criteria box in the design
view
of the Query? Or would I need to know VB or SQL? I'll be honest, I
don't have
any experience in those two, so any help would be greatly appreciated!

Thanks

 

[Ken Sheridan]

Have you added the GetToken and InParam functions from the MS site to
a module?

The second error BTW resulted from the line in my post having wrapped
in your newsreader, something you need to watch out for with messages
here.

Ken Sheridan
Stafford, England

Ken-

I tried the code that you provided. I seem to have run into a problem
though. When I try and run the query it gives me an error message that says
"Undefined function'InParam' in Expression."

Here is a copy of my SQL code for my Query.

SELECT CPM.[Ship Week], CPM.[Destination State], CPM.[Avg CPM], CPM.[Avg
RPM], [Weekly Fuel Rates].[Fuel Price]
FROM [Weekly Fuel Rates] INNER JOIN CPM ON [Weekly Fuel
Rates].Week=CPM.[Ship Week]
WHERE (((CPM.[Ship Week]) Between (Forms!frmStartupscreen!StartDate) And
(Forms!frmStartupscreen!EndDate)) And
((InParam([DestinationState],Forms!frmStartupscreen!txtStatesHidden))=True));

Perhaps my code is wrong? I'm still pretty new at this and not exactly sure
what I am looking at.

Any ideas what I could be doing wrong?

Thanks

To reference the selections form the list box as a parameter you'd
firstly have to build a value list in a hidden text box control so
that the query has something to reference. However, the IN operator
does not accept a parameter as its argument, so you have to simulate
it. You'll find a couple of ways of doing this at:

I'd recommend the second solution, using the InParam function as this
makes building the value list more straightforward as it caters for
any data type in exactly the same way, and you don't have to cater for
the possibility of a value being a substring of another value. So
using that method, if the hidden text box on the form is named
txtStatesHidden say, and the list box is named States the code to fill
the text box, which you'd put in the button's Click event procedure
before code to open the report etc, would be like this to open a
report in print preview for example:

Dim varItem As Variant
Dim strStatesList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.States

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStatesList = strStatesList & "," & ctrl.ItemData
(varItem)
Next varItem

' remove leading comma
strStatesList = Mid(strStatesList, 2)

' assign value list to hidden text box
Me.txtStatesHidden = strStatesList

DoCmd.OpenReport "YourReport", _
View:=acViewPreview
Else
MsgBox "No States Selected", vbInformation, "Warning"
End If

The query would be restricted as follows:

WHERE InParam(State, Forms!YourForm!txtStatesHidden)

where State is the name of the column in the table. In query design
view you'd enter InParam([State], Forms!YourForm!txtStatesHidden) into
the 'field' row of a blank column in the design grid, uncheck the
'show' check box, and in the column's 'criteria' row you'd enter True.

An alternative approach would be not to restrict the query at all, so
you don't need the hidden text box or the InParam function, but to
build a string expression for the WhereCondtion of the OpenReport
method:

Dim varItem As Variant
Dim strStatesList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.States

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStatesList = strStatesList & ",""" & ctrl.ItemData
(varItem) & """"
Next varItem

' remove leading comma
strStatesList = Mid(strStatesList, 2)

strCriteria = "State In(" & strStatesList & ")"

DoCmd.OpenReport "YourReport", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No States Selected", vbInformation, "Warning"
End If

This assumes that the State column in the table is a text data type
rather than a 'surrogate' numeric ID value.

Ken Sheridan
Stafford, England

 

[Ashley]

For whatever reason it did not save the Module when I entered it. However,
now it is still prompting me to enter in a State when I go to run the report.
It's almost as if it's not reading the List box at all. What have I done?
Obviously I haven't done something right.

Here's the query SQL:

SELECT CPM.[Ship Week], CPM.[Destination State], CPM.[Avg CPM], CPM.[Avg
RPM], [Weekly Fuel Rates].[Fuel Price]
FROM [Weekly Fuel Rates] INNER JOIN CPM ON [Weekly Fuel Rates].Week =
CPM.[Ship Week]
WHERE (((CPM.[Ship Week]) Between ([Forms]![frmStartupscreen]![StartDate])
And ([Forms]![frmStartupscreen]![EndDate])) AND
((InParam([DestinationState],[Forms]![frmStartupscreen]![txtStatesHidden]))=True));

and the button SQL

Private Sub btnChart1_Click()

Dim varItem As Variant
Dim strStatesList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.States

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStatesList = strStatesList & "," & ctrl.ItemData(varItem)

Next varItem

' remove leading comma
strStatesList = Mid(strStatesList, 2)

' assign value list to hidden text box
Me.txtStatesHidden = strStatesList

DoCmd.OpenForm "Cost/Rate Per Mile", acFormPivotChart


Else
MsgBox "No States Selected", vbInformation, "Warning"
End If
End Sub

Any ideas what I have done?

Thank you for your help I really appreciate it!

Have you added the GetToken and InParam functions from the MS site to
a module?

The second error BTW resulted from the line in my post having wrapped
in your newsreader, something you need to watch out for with messages
here.

Ken Sheridan
Stafford, England

Ken-

I tried the code that you provided. I seem to have run into a problem
though. When I try and run the query it gives me an error message that says
"Undefined function'InParam' in Expression."

Here is a copy of my SQL code for my Query.

SELECT CPM.[Ship Week], CPM.[Destination State], CPM.[Avg CPM], CPM.[Avg
RPM], [Weekly Fuel Rates].[Fuel Price]
FROM [Weekly Fuel Rates] INNER JOIN CPM ON [Weekly Fuel
Rates].Week=CPM.[Ship Week]
WHERE (((CPM.[Ship Week]) Between (Forms!frmStartupscreen!StartDate) And
(Forms!frmStartupscreen!EndDate)) And
((InParam([DestinationState],Forms!frmStartupscreen!txtStatesHidden))=True));

Perhaps my code is wrong? I'm still pretty new at this and not exactly sure
what I am looking at.

Any ideas what I could be doing wrong?

Thanks

To reference the selections form the list box as a parameter you'd
firstly have to build a value list in a hidden text box control so
that the query has something to reference. However, the IN operator
does not accept a parameter as its argument, so you have to simulate
it. You'll find a couple of ways of doing this at:

I'd recommend the second solution, using the InParam function as this
makes building the value list more straightforward as it caters for
any data type in exactly the same way, and you don't have to cater for
the possibility of a value being a substring of another value. So
using that method, if the hidden text box on the form is named
txtStatesHidden say, and the list box is named States the code to fill
the text box, which you'd put in the button's Click event procedure
before code to open the report etc, would be like this to open a
report in print preview for example:

Dim varItem As Variant
Dim strStatesList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.States

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStatesList = strStatesList & "," & ctrl.ItemData
(varItem)
Next varItem

' remove leading comma
strStatesList = Mid(strStatesList, 2)

' assign value list to hidden text box
Me.txtStatesHidden = strStatesList

DoCmd.OpenReport "YourReport", _
View:=acViewPreview
Else
MsgBox "No States Selected", vbInformation, "Warning"
End If

The query would be restricted as follows:

WHERE InParam(State, Forms!YourForm!txtStatesHidden)

where State is the name of the column in the table. In query design
view you'd enter InParam([State], Forms!YourForm!txtStatesHidden) into
the 'field' row of a blank column in the design grid, uncheck the
'show' check box, and in the column's 'criteria' row you'd enter True.

An alternative approach would be not to restrict the query at all, so
you don't need the hidden text box or the InParam function, but to
build a string expression for the WhereCondtion of the OpenReport
method:

Dim varItem As Variant
Dim strStatesList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.States

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStatesList = strStatesList & ",""" & ctrl.ItemData
(varItem) & """"
Next varItem

' remove leading comma
strStatesList = Mid(strStatesList, 2)

strCriteria = "State In(" & strStatesList & ")"

DoCmd.OpenReport "YourReport", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No States Selected", vbInformation, "Warning"
End If

This assumes that the State column in the table is a text data type
rather than a 'surrogate' numeric ID value.

Ken Sheridan
Stafford, England

I have created a form that displays when I startup the database called
frmStartupscreen. On that form are 3 command buttons to run a form, report
and chart. There is also a place to select multiple states out of a list box.
the list box is named States. What I would like to do is take the selections
from the States list box and apply it to a query named CPM Query that runs
the report and chart.

Is there a way that I can do that using the criteria box in the design view
of the Query? Or would I need to know VB or SQL? I'll be honest, I don't have
any experience in those two, so any help would be greatly appreciated!

Thanks

 

[Ken Sheridan]

I think it may simply be that the reference to the Destination State
column as the first argument of the InParam function is missing the
space.

If that doesn't cure it I'd suggest that you firstly make sure the
value list is being built correctly by commenting out the following
line like so:

' DoCmd.OpenForm "Cost/Rate Per Mile", acFormPivotChart

and temporarily setting the txtStatesHidden control's Visible property
to True (Yes) so you can see the values generated. Then open the form
and click the btnChart1 button

If the value list is being built correctly next check to see if the
query is returning the correct records by opening it independently
from the database window while the form is still open and the text box
contains the value list. If it returns the correct rows (and it looks
OK to me) then the problem would seem to be with the Cost/Rate Per
Mile form, so you'll need to debug that. The prompt for the state
could well be aruising from something in the form which is being
treated as a parameter.

One additional thing, if the start and end date parameters are actual
dates rather than week numbers say, I would recommend is that you
declare the date/time parameters in the query. Date/time parameters
can sometimes be misinterpreted as arithmetical expressions if not
declared, giving the wrong results. The SQL for the query would be:

PARAMETERS
Forms!frmStartupscreen!StartDate DATETIME,
Forms!frmStartupscreen!EndDate DATETIME;
SELECT CPM.[Ship Week], CPM.[Destination State],
CPM.[Avg CPM], CPM.[Avg RPM], [Weekly Fuel Rates].[Fuel Price]
FROM [Weekly Fuel Rates] INNER JOIN CPM
ON [Weekly Fuel Rates].Week=CPM.[Ship Week]
WHERE CPM.[Ship Week] BETWEEN Forms!frmStartupscreen!StartDate
AND Forms!frmStartupscreen!EndDate AND
InParam([Destination State],Forms!frmStartupscreen!txtStatesHidden)
=TRUE;

Ken Sheridan
Stafford, England

For whatever reason it did not save the Module when I entered it. However,
now it is still prompting me to enter in a State when I go to run the report.
It's almost as if it's not reading the List box at all. What have I done?
Obviously I haven't done something right.

Here's the query SQL:

SELECT CPM.[Ship Week], CPM.[Destination State], CPM.[Avg CPM], CPM.[Avg
RPM], [Weekly Fuel Rates].[Fuel Price]
FROM [Weekly Fuel Rates] INNER JOIN CPM ON [Weekly Fuel Rates].Week =
CPM.[Ship Week]
WHERE (((CPM.[Ship Week]) Between ([Forms]![frmStartupscreen]![StartDate])
And ([Forms]![frmStartupscreen]![EndDate])) AND
((InParam([DestinationState],[Forms]![frmStartupscreen]![txtStatesHidden]))=True));

and the button SQL

Private Sub btnChart1_Click()

Dim varItem As Variant
Dim strStatesList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.States

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStatesList = strStatesList & "," & ctrl.ItemData(varItem)

Next varItem

' remove leading comma
strStatesList = Mid(strStatesList, 2)

' assign value list to hidden text box
Me.txtStatesHidden = strStatesList

DoCmd.OpenForm "Cost/Rate Per Mile", acFormPivotChart

Else
MsgBox "No States Selected", vbInformation, "Warning"
End If
End Sub

Any ideas what I have done?

Thank you for your help I really appreciate it!

Have you added the GetToken and InParam functions from the MS site to
a module?

The second error BTW resulted from the line in my post having wrapped
in your newsreader, something you need to watch out for with messages
here.

Ken Sheridan
Stafford, England

Ken-
I tried the code that you provided. I seem to have run into a problem
though. When I try and run the query it gives me an error message that says
"Undefined function'InParam' in Expression."
Here is a copy of my SQL code for my Query.
SELECT CPM.[Ship Week], CPM.[Destination State], CPM.[Avg CPM], CPM.[Avg
RPM], [Weekly Fuel Rates].[Fuel Price]
FROM [Weekly Fuel Rates] INNER JOIN CPM ON [Weekly Fuel
Rates].Week=CPM.[Ship Week]
WHERE (((CPM.[Ship Week]) Between (Forms!frmStartupscreen!StartDate) And
(Forms!frmStartupscreen!EndDate)) And
((InParam([DestinationState],Forms!frmStartupscreen!txtStatesHidden))=True));
Perhaps my code is wrong? I'm still pretty new at this and not exactly sure
what I am looking at.
Any ideas what I could be doing wrong?
Thanks
:
To reference the selections form the list box as a parameter you'd
firstly have to build a value list in a hidden text box control so
that the query has something to reference. However, the IN operator
does not accept a parameter as its argument, so you have to simulate
it. You'll find a couple of ways of doing this at:
http://support.microsoft.com/kb/100131/en-us
I'd recommend the second solution, using the InParam function as this
makes building the value list more straightforward as it caters for
any data type in exactly the same way, and you don't have to cater for
the possibility of a value being a substring of another value. So
using that method, if the hidden text box on the form is named
txtStatesHidden say, and the list box is named States the code to fill
the text box, which you'd put in the button's Click event procedure
before code to open the report etc, would be like this to open a
report in print preview for example:
Dim varItem As Variant
Dim strStatesList As String
Dim strCriteria As String
Dim ctrl As Control
Set ctrl = Me.States
' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStatesList = strStatesList & "," & ctrl.ItemData
(varItem)
Next varItem
' remove leading comma
strStatesList = Mid(strStatesList, 2)
' assign value list to hidden text box
Me.txtStatesHidden = strStatesList
DoCmd.OpenReport "YourReport", _
View:=acViewPreview
Else
MsgBox "No States Selected", vbInformation, "Warning"
End If
The query would be restricted as follows:
WHERE InParam(State, Forms!YourForm!txtStatesHidden)
where State is the name of the column in the table. In query design
view you'd enter InParam([State], Forms!YourForm!txtStatesHidden) into
the 'field' row of a blank column in the design grid, uncheck the
'show' check box, and in the column's 'criteria' row you'd enter True.
An alternative approach would be not to restrict the query at all, so
you don't need the hidden text box or the InParam function, but to
build a string expression for the WhereCondtion of the OpenReport
method:
Dim varItem As Variant
Dim strStatesList As String
Dim strCriteria As String
Dim ctrl As Control
Set ctrl = Me.States
' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStatesList = strStatesList & ",""" & ctrl.ItemData
(varItem) & """"
Next varItem
' remove leading comma
strStatesList = Mid(strStatesList, 2)
strCriteria = "State In(" & strStatesList & ")"
DoCmd.OpenReport "YourReport", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No States Selected", vbInformation, "Warning"
End If
This assumes that the State column in the table is a text data type
rather than a 'surrogate' numeric ID value.
Ken Sheridan
Stafford, England
I have created a form that displays when I startup the database called
frmStartupscreen. On that form are 3 command buttons to run a form, report
and chart. There is also a place to select multiple states out of a list box.
the list box is named States. What I would like to do is take the selections
from the States list box and apply it to a query named CPM Query that runs
the report and chart.
Is there a way that I can do that using the criteria box in the design view
of the Query? Or would I need to know VB or SQL? I'll be honest, I don't have
any experience in those two, so any help would be greatly appreciated!
Thanks

 

[Ashley]

Thank you so much that did it!

Have a great day!

I think it may simply be that the reference to the Destination State
column as the first argument of the InParam function is missing the
space.

If that doesn't cure it I'd suggest that you firstly make sure the
value list is being built correctly by commenting out the following
line like so:

' DoCmd.OpenForm "Cost/Rate Per Mile", acFormPivotChart

and temporarily setting the txtStatesHidden control's Visible property
to True (Yes) so you can see the values generated. Then open the form
and click the btnChart1 button

If the value list is being built correctly next check to see if the
query is returning the correct records by opening it independently
from the database window while the form is still open and the text box
contains the value list. If it returns the correct rows (and it looks
OK to me) then the problem would seem to be with the Cost/Rate Per
Mile form, so you'll need to debug that. The prompt for the state
could well be aruising from something in the form which is being
treated as a parameter.

One additional thing, if the start and end date parameters are actual
dates rather than week numbers say, I would recommend is that you
declare the date/time parameters in the query. Date/time parameters
can sometimes be misinterpreted as arithmetical expressions if not
declared, giving the wrong results. The SQL for the query would be:

PARAMETERS
Forms!frmStartupscreen!StartDate DATETIME,
Forms!frmStartupscreen!EndDate DATETIME;
SELECT CPM.[Ship Week], CPM.[Destination State],
CPM.[Avg CPM], CPM.[Avg RPM], [Weekly Fuel Rates].[Fuel Price]
FROM [Weekly Fuel Rates] INNER JOIN CPM
ON [Weekly Fuel Rates].Week=CPM.[Ship Week]
WHERE CPM.[Ship Week] BETWEEN Forms!frmStartupscreen!StartDate
AND Forms!frmStartupscreen!EndDate AND
InParam([Destination State],Forms!frmStartupscreen!txtStatesHidden)
=TRUE;

Ken Sheridan
Stafford, England

For whatever reason it did not save the Module when I entered it. However,
now it is still prompting me to enter in a State when I go to run the report.
It's almost as if it's not reading the List box at all. What have I done?
Obviously I haven't done something right.

Here's the query SQL:

SELECT CPM.[Ship Week], CPM.[Destination State], CPM.[Avg CPM], CPM.[Avg
RPM], [Weekly Fuel Rates].[Fuel Price]
FROM [Weekly Fuel Rates] INNER JOIN CPM ON [Weekly Fuel Rates].Week =
CPM.[Ship Week]
WHERE (((CPM.[Ship Week]) Between ([Forms]![frmStartupscreen]![StartDate])
And ([Forms]![frmStartupscreen]![EndDate])) AND
((InParam([DestinationState],[Forms]![frmStartupscreen]![txtStatesHidden]))=True));

and the button SQL

Private Sub btnChart1_Click()

Dim varItem As Variant
Dim strStatesList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.States

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStatesList = strStatesList & "," & ctrl.ItemData(varItem)

Next varItem

' remove leading comma
strStatesList = Mid(strStatesList, 2)

' assign value list to hidden text box
Me.txtStatesHidden = strStatesList

DoCmd.OpenForm "Cost/Rate Per Mile", acFormPivotChart

Else
MsgBox "No States Selected", vbInformation, "Warning"
End If
End Sub

Any ideas what I have done?

Thank you for your help I really appreciate it!

Have you added the GetToken and InParam functions from the MS site to
a module?

The second error BTW resulted from the line in my post having wrapped
in your newsreader, something you need to watch out for with messages
here.

Ken Sheridan
Stafford, England

Ken-

I tried the code that you provided. I seem to have run into a problem
though. When I try and run the query it gives me an error message that says
"Undefined function'InParam' in Expression."

Here is a copy of my SQL code for my Query.

SELECT CPM.[Ship Week], CPM.[Destination State], CPM.[Avg CPM], CPM.[Avg
RPM], [Weekly Fuel Rates].[Fuel Price]
FROM [Weekly Fuel Rates] INNER JOIN CPM ON [Weekly Fuel
Rates].Week=CPM.[Ship Week]
WHERE (((CPM.[Ship Week]) Between (Forms!frmStartupscreen!StartDate) And
(Forms!frmStartupscreen!EndDate)) And
((InParam([DestinationState],Forms!frmStartupscreen!txtStatesHidden))=True));

Perhaps my code is wrong? I'm still pretty new at this and not exactly sure
what I am looking at.

Any ideas what I could be doing wrong?

:
To reference the selections form the list box as a parameter you'd
firstly have to build a value list in a hidden text box control so
that the query has something to reference. However, the IN operator
does not accept a parameter as its argument, so you have to simulate
it. You'll find a couple of ways of doing this at:

I'd recommend the second solution, using the InParam function as this
makes building the value list more straightforward as it caters for
any data type in exactly the same way, and you don't have to cater for
the possibility of a value being a substring of another value. So
using that method, if the hidden text box on the form is named
txtStatesHidden say, and the list box is named States the code to fill
the text box, which you'd put in the button's Click event procedure
before code to open the report etc, would be like this to open a
report in print preview for example:

Dim varItem As Variant
Dim strStatesList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.States

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStatesList = strStatesList & "," & ctrl.ItemData
(varItem)
Next varItem

' remove leading comma
strStatesList = Mid(strStatesList, 2)

' assign value list to hidden text box
Me.txtStatesHidden = strStatesList

DoCmd.OpenReport "YourReport", _
View:=acViewPreview
Else
MsgBox "No States Selected", vbInformation, "Warning"
End If

The query would be restricted as follows:

WHERE InParam(State, Forms!YourForm!txtStatesHidden)

where State is the name of the column in the table. In query design
view you'd enter InParam([State], Forms!YourForm!txtStatesHidden) into
the 'field' row of a blank column in the design grid, uncheck the
'show' check box, and in the column's 'criteria' row you'd enter True.

An alternative approach would be not to restrict the query at all, so
you don't need the hidden text box or the InParam function, but to
build a string expression for the WhereCondtion of the OpenReport
method:

Dim varItem As Variant
Dim strStatesList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.States

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStatesList = strStatesList & ",""" & ctrl.ItemData
(varItem) & """"
Next varItem

' remove leading comma
strStatesList = Mid(strStatesList, 2)

strCriteria = "State In(" & strStatesList & ")"

DoCmd.OpenReport "YourReport", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No States Selected", vbInformation, "Warning"
End If

This assumes that the State column in the table is a text data type
rather than a 'surrogate' numeric ID value.

Ken Sheridan
Stafford, England

I have created a form that displays when I startup the database called
frmStartupscreen. On that form are 3 command buttons to run a form, report
and chart. There is also a place to select multiple states out of a list box.
the list box is named States. What I would like to do is take the selections
from the States list box and apply it to a query named CPM Query that runs
the report and chart.

Is there a way that I can do that using the criteria box in the design view
of the Query? Or would I need to know VB or SQL? I'll be honest, I don't have
any experience in those two, so any help would be greatly appreciated!

Thanks

 

[mora xavier]

Ken-

I tried the code that you provided. I seem to have run into a problem
though. When I try and run the query it gives me an error message that says
"Undefined function'InParam' in Expression."

Here is a copy of my SQL code for my Query.

SELECT CPM.[Ship Week], CPM.[Destination State], CPM.[Avg CPM], CPM.[Avg
RPM], [Weekly Fuel Rates].[Fuel Price]
FROM [Weekly Fuel Rates] INNER JOIN CPM ON [Weekly Fuel
Rates].Week=CPM.[Ship Week]
WHERE (((CPM.[Ship Week]) Between (Forms!frmStartupscreen!StartDate) And
(Forms!frmStartupscreen!EndDate)) And
((InParam([DestinationState],Forms!frmStartupscreen!txtStatesHidden))=True))
;

Perhaps my code is wrong? I'm still pretty new at this and not exactly sure
what I am looking at.

Any ideas what I could be doing wrong?

Thanks

To reference the selections form the list box as a parameter you'd
firstly have to build a value list in a hidden text box control so
that the query has something to reference. However, the IN operator
does not accept a parameter as its argument, so you have to simulate
it. You'll find a couple of ways of doing this at:

http://support.microsoft.com/kb/100131/en-us

I'd recommend the second solution, using the InParam function as this
makes building the value list more straightforward as it caters for
any data type in exactly the same way, and you don't have to cater for
the possibility of a value being a substring of another value. So
using that method, if the hidden text box on the form is named
txtStatesHidden say, and the list box is named States the code to fill
the text box, which you'd put in the button's Click event procedure
before code to open the report etc, would be like this to open a
report in print preview for example:

Dim varItem As Variant
Dim strStatesList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.States

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStatesList = strStatesList & "," & ctrl.ItemData
(varItem)
Next varItem

' remove leading comma
strStatesList = Mid(strStatesList, 2)

' assign value list to hidden text box
Me.txtStatesHidden = strStatesList

DoCmd.OpenReport "YourReport", _
View:=acViewPreview
Else
MsgBox "No States Selected", vbInformation, "Warning"
End If

The query would be restricted as follows:

WHERE InParam(State, Forms!YourForm!txtStatesHidden)

where State is the name of the column in the table. In query design
view you'd enter InParam([State], Forms!YourForm!txtStatesHidden) into
the 'field' row of a blank column in the design grid, uncheck the
'show' check box, and in the column's 'criteria' row you'd enter True.

An alternative approach would be not to restrict the query at all, so
you don't need the hidden text box or the InParam function, but to
build a string expression for the WhereCondtion of the OpenReport
method:

Dim varItem As Variant
Dim strStatesList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.States

' loop through list box's ItemsSelected collection
' and build comma separated list of selected items
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strStatesList = strStatesList & ",""" & ctrl.ItemData
(varItem) & """"
Next varItem

' remove leading comma
strStatesList = Mid(strStatesList, 2)

strCriteria = "State In(" & strStatesList & ")"

DoCmd.OpenReport "YourReport", _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No States Selected", vbInformation, "Warning"
End If

This assumes that the State column in the table is a text data type
rather than a 'surrogate' numeric ID value.

Ken Sheridan
Stafford, England

I have created a form that displays when I startup the database called
frmStartupscreen. On that form are 3 command buttons to run a form, report
and chart. There is also a place to select multiple states out of a list box.
the list box is named States. What I would like to do is take the selections
from the States list box and apply it to a query named CPM Query that runs
the report and chart.

Is there a way that I can do that using the criteria box in the design view
of the Query? Or would I need to know VB or SQL? I'll be honest, I don't have
any experience in those two, so any help would be greatly appreciated!

Thanks