List in the parameter box.

[Guest]

Is ther in the query in Access possible to create a parameter that present
you a list in the parameter box popping up, so you don’t have to write the
criteria but only to choose it.
Anyone who knows how to do this if it is possible, will you then please help
me.

 

[Jeff Boyce]

Not a way in a parameter in a query.

Is a way, though. Create a form that you'll use to feed the query the
parameter. Add a combo box to the form, based on the source of your list.
Modify your query to use that form, that combo box as its parameter.
Instead of something like:

[Please Enter Your Parameter]

in the query design, it will look something like:

Forms!YourNewForm!YourComboBox

Now add a command button to the form to run the query. Here's the sequence
of how it would work:

open the form (note: the form MUST be opened and the combo box

selection made for this to work)

 

[Guest]

Not a way in a parameter in a query.

Is a way, though. Create a form that you'll use to feed the query the
parameter. Add a combo box to the form, based on the source of your list.
Modify your query to use that form, that combo box as its parameter.
Instead of something like:

[Please Enter Your Parameter]

in the query design, it will look something like:

Forms!YourNewForm!YourComboBox

Now add a command button to the form to run the query. Here's the sequence
of how it would work:

open the form (note: the form MUST be opened and the combo box

selection made for this to work)

select a value from the combo box
click the command button
Access opens the query and notices that it needs a parameter
Access sees that the parameter is pointing at the form's combo box and looks there for a value
Access runs the query

--
Good luck

Jeff Boyce
<Access MVP>

Is ther in the query in Access possible to create a parameter that present
you a list in the parameter box popping up, so you don’t have to write the
criteria but only to choose it.
Anyone who knows how to do this if it is possible, will you then please help
me.

Thank you from Denmark to Jeff Boyce, works superb. I use it with a print
report within a macho function to decide what to print.

ronoee.

 

[Jeff Boyce]

I've created several systems that use a "Report Order" form. The form lists
available reports, off a table I create that holds the Access report name
and a "user" report name, plus other fields. Some of those fields allow me
to enable/disable selection controls on the form, so if a report needs a
criterion, the control that allows selection of that criterion gets enabled
when the report is selected.

Good luck!

Jeff Boyce
<Access MVP>

Not a way in a parameter in a query.

Is a way, though. Create a form that you'll use to feed the query the
parameter. Add a combo box to the form, based on the source of your list.
Modify your query to use that form, that combo box as its parameter.
Instead of something like:

[Please Enter Your Parameter]

in the query design, it will look something like:

Forms!YourNewForm!YourComboBox

Now add a command button to the form to run the query. Here's the sequence
of how it would work:

open the form (note: the form MUST be opened and the combo box

selection made for this to work)

select a value from the combo box
click the command button
Access opens the query and notices that it needs a parameter
Access sees that the parameter is pointing at the form's combo box

and
looks there for a value

Access runs the query

--
Good luck

Jeff Boyce
<Access MVP>

Is ther in the query in Access possible to create a parameter that present
you a list in the parameter box popping up, so you don’t have to write the
criteria but only to choose it.
Anyone who knows how to do this if it is possible, will you then

please
help

me.

Thank you from Denmark to Jeff Boyce, works superb. I use it with a print
report within a macho function to decide what to print.

ronoee.

 

[Guest]

Jeff -

These systems sound very interesting. Are you able to select multiple
criterion? Also, can you multi-select off a list box, instead of a combo box?

Thanks.

I've created several systems that use a "Report Order" form. The form lists
available reports, off a table I create that holds the Access report name
and a "user" report name, plus other fields. Some of those fields allow me
to enable/disable selection controls on the form, so if a report needs a
criterion, the control that allows selection of that criterion gets enabled
when the report is selected.

Good luck!

Jeff Boyce
<Access MVP>

Not a way in a parameter in a query.

Is a way, though. Create a form that you'll use to feed the query the
parameter. Add a combo box to the form, based on the source of your list.
Modify your query to use that form, that combo box as its parameter.
Instead of something like:

[Please Enter Your Parameter]

in the query design, it will look something like:

Forms!YourNewForm!YourComboBox

Now add a command button to the form to run the query. Here's the sequence
of how it would work:

open the form (note: the form MUST be opened and the combo box
selection made for this to work)
select a value from the combo box
click the command button
Access opens the query and notices that it needs a parameter
Access sees that the parameter is pointing at the form's combo box and
looks there for a value
Access runs the query

--
Good luck

Jeff Boyce
<Access MVP>

Is ther in the query in Access possible to create a parameter that present
you a list in the parameter box popping up, so you don’t have to write the
criteria but only to choose it.
Anyone who knows how to do this if it is possible, will you then please
help
me.

Thank you from Denmark to Jeff Boyce, works superb. I use it with a print
report within a macho function to decide what to print.

ronoee.

 

[Jeff Boyce]

I use this approach to (optionally) select one or more criteria. I don't
use a multi-select listbox, as I've not had the need/requirement yet to
select more than one of some category. I believe it would be a matter of
writing the code to loop through the SelectedItem collection for such a
listbox, appending the selected items to the WHERE clause.

--
Good luck

Jeff Boyce
<Access MVP>

Jeff -

These systems sound very interesting. Are you able to select multiple
criterion? Also, can you multi-select off a list box, instead of a combo box?

Thanks.

I've created several systems that use a "Report Order" form. The form lists
available reports, off a table I create that holds the Access report name
and a "user" report name, plus other fields. Some of those fields allow me
to enable/disable selection controls on the form, so if a report needs a
criterion, the control that allows selection of that criterion gets enabled
when the report is selected.

Good luck!

Jeff Boyce
<Access MVP>

:

Not a way in a parameter in a query.

Is a way, though. Create a form that you'll use to feed the query the
parameter. Add a combo box to the form, based on the source of your list.
Modify your query to use that form, that combo box as its parameter.
Instead of something like:

[Please Enter Your Parameter]

in the query design, it will look something like:

Forms!YourNewForm!YourComboBox

Now add a command button to the form to run the query. Here's the sequence
of how it would work:

open the form (note: the form MUST be opened and the combo box
selection made for this to work)
select a value from the combo box
click the command button
Access opens the query and notices that it needs a parameter
Access sees that the parameter is pointing at the form's combo

box
and

looks there for a value
Access runs the query

--
Good luck

Jeff Boyce
<Access MVP>

Is ther in the query in Access possible to create a parameter that present
you a list in the parameter box popping up, so you don’t have to

write
the

criteria but only to choose it.
Anyone who knows how to do this if it is possible, will you then please
help
me.





Thank you from Denmark to Jeff Boyce, works superb. I use it with a print
report within a macho function to decide what to print.

ronoee.

 

[Guest]

I tried what you show below, but can't get it to work.
I have a query that currently has
[Enter Program Name]
in the Criteria field for a field called Program Name
Instead of having a free form text field, I want to use a list box so that
users can pick from the list. The name of the form is PROGRAM. on that
form, I have a combo box that shows the valid values and a command button to
run the query...

I tried entering what you said all of these ways:
Forms![PROGRAM]
[Forms]![PROGRAM]
Forms![PROGRAM]![Item Number]

and nothing will work. I get syntax errors, etc. and can't get the PROGRAM
form to display... Help please!!!

Not a way in a parameter in a query.

Is a way, though. Create a form that you'll use to feed the query the
parameter. Add a combo box to the form, based on the source of your list.
Modify your query to use that form, that combo box as its parameter.
Instead of something like:

[Please Enter Your Parameter]

in the query design, it will look something like:

Forms!YourNewForm!YourComboBox

Now add a command button to the form to run the query. Here's the sequence
of how it would work:

open the form (note: the form MUST be opened and the combo box

selection made for this to work)

select a value from the combo box
click the command button
Access opens the query and notices that it needs a parameter
Access sees that the parameter is pointing at the form's combo box and looks there for a value
Access runs the query

--
Good luck

Jeff Boyce
<Access MVP>

Is ther in the query in Access possible to create a parameter that present
you a list in the parameter box popping up, so you don’t have to write the
criteria but only to choose it.
Anyone who knows how to do this if it is possible, will you then please help
me.

 

[Jeff Boyce]

Keri

Any chance the field in question has been defined as a "lookup" data type in
the table? If so, you've run afoul of an issue often discussed in
tablesdbdesign. That is, lookup data types store one value but display
something else.

If you are NOT working with a lookup field, then the next most likely
culprit is a spelling error. You didn't provide any information about how
it doesn't work, so it's a bit tough trying to guess what might be wrong/not
working...

--
More info, please ...

Jeff Boyce
<Access MVP>

I tried what you show below, but can't get it to work.
I have a query that currently has
[Enter Program Name]
in the Criteria field for a field called Program Name
Instead of having a free form text field, I want to use a list box so that
users can pick from the list. The name of the form is PROGRAM. on that
form, I have a combo box that shows the valid values and a command button to
run the query...

I tried entering what you said all of these ways:
Forms![PROGRAM]
[Forms]![PROGRAM]
Forms![PROGRAM]![Item Number]

and nothing will work. I get syntax errors, etc. and can't get the PROGRAM
form to display... Help please!!!

Not a way in a parameter in a query.

Is a way, though. Create a form that you'll use to feed the query the
parameter. Add a combo box to the form, based on the source of your list.
Modify your query to use that form, that combo box as its parameter.
Instead of something like:

[Please Enter Your Parameter]

in the query design, it will look something like:

Forms!YourNewForm!YourComboBox

Now add a command button to the form to run the query. Here's the sequence
of how it would work:

open the form (note: the form MUST be opened and the combo box

selection made for this to work)

select a value from the combo box
click the command button
Access opens the query and notices that it needs a parameter
Access sees that the parameter is pointing at the form's combo box

and
looks there for a value

Access runs the query

--
Good luck

Jeff Boyce
<Access MVP>

Is ther in the query in Access possible to create a parameter that present
you a list in the parameter box popping up, so you don’t have to write the
criteria but only to choose it.
Anyone who knows how to do this if it is possible, will you then

please
help

me.

 

[Guest]

You are good!
Yes, the field in question is defined as a lookup data type. There are two
tables that use the Program Name field. One table (tblPrograms) stores the
list of the valid Program Names. The other table (tblItemsForAssignment)
stores the projects related to the programs, and has a Program Name field to
relate a project to a specific Program. To ensure consistency in the Program
names (for reporting, etc.), I have the Program Name field in
tblItemsForAssignment as a lookup field, pointing to the Program Name field
in tblPrograms.

I thought of having the query point to the Program Name field tblPrograms
instead of tblItemsForAssignment. But when I try this, and enter
Forms![PROGRAM]![Item Number] in the Criteria field, MS Access changes it to
[Forms]![PROGRAM]![Item Number] and then displays a dialog box with
"Forms!PROGRAM!Item Number" and a text input box...

Hope that's enough info...

Keri

Any chance the field in question has been defined as a "lookup" data type in
the table? If so, you've run afoul of an issue often discussed in
tablesdbdesign. That is, lookup data types store one value but display
something else.

If you are NOT working with a lookup field, then the next most likely
culprit is a spelling error. You didn't provide any information about how
it doesn't work, so it's a bit tough trying to guess what might be wrong/not
working...

--
More info, please ...

Jeff Boyce
<Access MVP>

I tried what you show below, but can't get it to work.
I have a query that currently has
[Enter Program Name]
in the Criteria field for a field called Program Name
Instead of having a free form text field, I want to use a list box so that
users can pick from the list. The name of the form is PROGRAM. on that
form, I have a combo box that shows the valid values and a command button to
run the query...

I tried entering what you said all of these ways:
Forms![PROGRAM]
[Forms]![PROGRAM]
Forms![PROGRAM]![Item Number]

and nothing will work. I get syntax errors, etc. and can't get the PROGRAM
form to display... Help please!!!

Not a way in a parameter in a query.

Is a way, though. Create a form that you'll use to feed the query the
parameter. Add a combo box to the form, based on the source of your list.
Modify your query to use that form, that combo box as its parameter.
Instead of something like:

[Please Enter Your Parameter]

in the query design, it will look something like:

Forms!YourNewForm!YourComboBox

Now add a command button to the form to run the query. Here's the sequence
of how it would work:

open the form (note: the form MUST be opened and the combo box
selection made for this to work)
select a value from the combo box
click the command button
Access opens the query and notices that it needs a parameter
Access sees that the parameter is pointing at the form's combo box and
looks there for a value
Access runs the query

--
Good luck

Jeff Boyce
<Access MVP>

Is ther in the query in Access possible to create a parameter that present
you a list in the parameter box popping up, so you don’t have to write the
criteria but only to choose it.
Anyone who knows how to do this if it is possible, will you then please
help
me.

 

[Jeff Boyce]

Keri

Change the data type of the Program field in tblItemsForAssignment to a
?LongInt (this assumes your primary key in tblPrograms is an Autonumber - if
not, change to a corresponding/compatible type). Now your tblItemsFor...
table displays what it holds.

Create a query that joins tblItemsFor ... and tblPrograms on this field.
The value you are looking for is the key value.

In your form, build a combo box based on the tblProgram ID field, plus the
ProgramName (or whatever you've called it). Make the first column (the ID)
of zero-width in the properties -- this displays the ProgramName first in
the combo box. When you've picked a Program, the combo box's value is the
first column (i.e., the ID).

Now have your query look to the form, to the combo box, for the ID value it
needs.

--
Good luck

Jeff Boyce
<Access MVP>

You are good!
Yes, the field in question is defined as a lookup data type. There are two
tables that use the Program Name field. One table (tblPrograms) stores the
list of the valid Program Names. The other table (tblItemsForAssignment)
stores the projects related to the programs, and has a Program Name field to
relate a project to a specific Program. To ensure consistency in the Program
names (for reporting, etc.), I have the Program Name field in
tblItemsForAssignment as a lookup field, pointing to the Program Name field
in tblPrograms.

I thought of having the query point to the Program Name field tblPrograms
instead of tblItemsForAssignment. But when I try this, and enter
Forms![PROGRAM]![Item Number] in the Criteria field, MS Access changes it to
[Forms]![PROGRAM]![Item Number] and then displays a dialog box with
"Forms!PROGRAM!Item Number" and a text input box...

Hope that's enough info...

Keri

Any chance the field in question has been defined as a "lookup" data type in
the table? If so, you've run afoul of an issue often discussed in
tablesdbdesign. That is, lookup data types store one value but display
something else.

If you are NOT working with a lookup field, then the next most likely
culprit is a spelling error. You didn't provide any information about how
it doesn't work, so it's a bit tough trying to guess what might be wrong/not
working...

--
More info, please ...

Jeff Boyce
<Access MVP>

I tried what you show below, but can't get it to work.
I have a query that currently has
[Enter Program Name]
in the Criteria field for a field called Program Name
Instead of having a free form text field, I want to use a list box so that
users can pick from the list. The name of the form is PROGRAM. on that
form, I have a combo box that shows the valid values and a command

button
to

run the query...

I tried entering what you said all of these ways:
Forms![PROGRAM]
[Forms]![PROGRAM]
Forms![PROGRAM]![Item Number]

and nothing will work. I get syntax errors, etc. and can't get the PROGRAM
form to display... Help please!!!

:

Not a way in a parameter in a query.

Is a way, though. Create a form that you'll use to feed the query the
parameter. Add a combo box to the form, based on the source of your list.
Modify your query to use that form, that combo box as its parameter.
Instead of something like:

[Please Enter Your Parameter]

in the query design, it will look something like:

Forms!YourNewForm!YourComboBox

Now add a command button to the form to run the query. Here's the sequence
of how it would work:

open the form (note: the form MUST be opened and the combo box
selection made for this to work)
select a value from the combo box
click the command button
Access opens the query and notices that it needs a parameter
Access sees that the parameter is pointing at the form's combo

box
and

looks there for a value
Access runs the query

--
Good luck

Jeff Boyce
<Access MVP>

Is ther in the query in Access possible to create a parameter that present
you a list in the parameter box popping up, so you don’t have to

write
the

criteria but only to choose it.
Anyone who knows how to do this if it is possible, will you then please
help
me.

 

[Guest]

The key in tblItemsForAssignment is NOT an AutoNumber - it is text (because
it can be a combination of letters and numbers). What I don't understand is
what a corresponding/compatible type would be... the Program field is
currently TEXT

Keri

Change the data type of the Program field in tblItemsForAssignment to a
?LongInt (this assumes your primary key in tblPrograms is an Autonumber - if
not, change to a corresponding/compatible type). Now your tblItemsFor...
table displays what it holds.

Create a query that joins tblItemsFor ... and tblPrograms on this field.
The value you are looking for is the key value.

In your form, build a combo box based on the tblProgram ID field, plus the
ProgramName (or whatever you've called it). Make the first column (the ID)
of zero-width in the properties -- this displays the ProgramName first in
the combo box. When you've picked a Program, the combo box's value is the
first column (i.e., the ID).

Now have your query look to the form, to the combo box, for the ID value it
needs.

--
Good luck

Jeff Boyce
<Access MVP>

You are good!
Yes, the field in question is defined as a lookup data type. There are two
tables that use the Program Name field. One table (tblPrograms) stores the
list of the valid Program Names. The other table (tblItemsForAssignment)
stores the projects related to the programs, and has a Program Name field to
relate a project to a specific Program. To ensure consistency in the Program
names (for reporting, etc.), I have the Program Name field in
tblItemsForAssignment as a lookup field, pointing to the Program Name field
in tblPrograms.

I thought of having the query point to the Program Name field tblPrograms
instead of tblItemsForAssignment. But when I try this, and enter
Forms![PROGRAM]![Item Number] in the Criteria field, MS Access changes it to
[Forms]![PROGRAM]![Item Number] and then displays a dialog box with
"Forms!PROGRAM!Item Number" and a text input box...

Hope that's enough info...

Keri

Any chance the field in question has been defined as a "lookup" data type in
the table? If so, you've run afoul of an issue often discussed in
tablesdbdesign. That is, lookup data types store one value but display
something else.

If you are NOT working with a lookup field, then the next most likely
culprit is a spelling error. You didn't provide any information about how
it doesn't work, so it's a bit tough trying to guess what might be wrong/not
working...

--
More info, please ...

Jeff Boyce
<Access MVP>

I tried what you show below, but can't get it to work.
I have a query that currently has
[Enter Program Name]
in the Criteria field for a field called Program Name
Instead of having a free form text field, I want to use a list box so that
users can pick from the list. The name of the form is PROGRAM. on that
form, I have a combo box that shows the valid values and a command button
to
run the query...

I tried entering what you said all of these ways:
Forms![PROGRAM]
[Forms]![PROGRAM]
Forms![PROGRAM]![Item Number]

and nothing will work. I get syntax errors, etc. and can't get the
PROGRAM
form to display... Help please!!!

:

Not a way in a parameter in a query.

Is a way, though. Create a form that you'll use to feed the query the
parameter. Add a combo box to the form, based on the source of your
list.
Modify your query to use that form, that combo box as its parameter.
Instead of something like:

[Please Enter Your Parameter]

in the query design, it will look something like:

Forms!YourNewForm!YourComboBox

Now add a command button to the form to run the query. Here's the
sequence
of how it would work:

open the form (note: the form MUST be opened and the combo box
selection made for this to work)
select a value from the combo box
click the command button
Access opens the query and notices that it needs a parameter
Access sees that the parameter is pointing at the form's combo box
and
looks there for a value
Access runs the query

--
Good luck

Jeff Boyce
<Access MVP>

Is ther in the query in Access possible to create a parameter that
present
you a list in the parameter box popping up, so you don’t have to write
the
criteria but only to choose it.
Anyone who knows how to do this if it is possible, will you then
please
help
me.

 

[Jeff Boyce]

Text is a type compatible with Text. Both need to be the same length.


--
Good luck

Jeff Boyce
<Access MVP>

The key in tblItemsForAssignment is NOT an AutoNumber - it is text (because
it can be a combination of letters and numbers). What I don't understand is
what a corresponding/compatible type would be... the Program field is
currently TEXT

Keri

Change the data type of the Program field in tblItemsForAssignment to a
?LongInt (this assumes your primary key in tblPrograms is an Autonumber - if
not, change to a corresponding/compatible type). Now your tblItemsFor...
table displays what it holds.

Create a query that joins tblItemsFor ... and tblPrograms on this field.
The value you are looking for is the key value.

In your form, build a combo box based on the tblProgram ID field, plus the
ProgramName (or whatever you've called it). Make the first column (the ID)
of zero-width in the properties -- this displays the ProgramName first in
the combo box. When you've picked a Program, the combo box's value is the
first column (i.e., the ID).

Now have your query look to the form, to the combo box, for the ID value it
needs.

--
Good luck

Jeff Boyce
<Access MVP>

You are good!
Yes, the field in question is defined as a lookup data type. There are two
tables that use the Program Name field. One table (tblPrograms)

stores
the

list of the valid Program Names. The other table (tblItemsForAssignment)
stores the projects related to the programs, and has a Program Name

field
to

relate a project to a specific Program. To ensure consistency in the Program
names (for reporting, etc.), I have the Program Name field in
tblItemsForAssignment as a lookup field, pointing to the Program Name field
in tblPrograms.

I thought of having the query point to the Program Name field tblPrograms
instead of tblItemsForAssignment. But when I try this, and enter
Forms![PROGRAM]![Item Number] in the Criteria field, MS Access changes

it
to

[Forms]![PROGRAM]![Item Number] and then displays a dialog box with
"Forms!PROGRAM!Item Number" and a text input box...

Hope that's enough info...

:

Keri

Any chance the field in question has been defined as a "lookup" data type in
the table? If so, you've run afoul of an issue often discussed in
tablesdbdesign. That is, lookup data types store one value but display
something else.

If you are NOT working with a lookup field, then the next most likely
culprit is a spelling error. You didn't provide any information

about
how

it doesn't work, so it's a bit tough trying to guess what might be wrong/not
working...

--
More info, please ...

Jeff Boyce
<Access MVP>

I tried what you show below, but can't get it to work.
I have a query that currently has
[Enter Program Name]
in the Criteria field for a field called Program Name
Instead of having a free form text field, I want to use a list box

so
that

users can pick from the list. The name of the form is PROGRAM.

on
that

form, I have a combo box that shows the valid values and a command button
to
run the query...

I tried entering what you said all of these ways:
Forms![PROGRAM]
[Forms]![PROGRAM]
Forms![PROGRAM]![Item Number]

and nothing will work. I get syntax errors, etc. and can't get the
PROGRAM
form to display... Help please!!!

:

Not a way in a parameter in a query.

Is a way, though. Create a form that you'll use to feed the

query
the

parameter. Add a combo box to the form, based on the source of your
list.
Modify your query to use that form, that combo box as its parameter.
Instead of something like:

[Please Enter Your Parameter]

in the query design, it will look something like:

Forms!YourNewForm!YourComboBox

Now add a command button to the form to run the query. Here's the
sequence
of how it would work:

open the form (note: the form MUST be opened and the combo box
selection made for this to work)
select a value from the combo box
click the command button
Access opens the query and notices that it needs a parameter
Access sees that the parameter is pointing at the form's

combo
box

and
looks there for a value
Access runs the query

--
Good luck

Jeff Boyce
<Access MVP>

Is ther in the query in Access possible to create a parameter that
present
you a list in the parameter box popping up, so you don’t have

to
write

the
criteria but only to choose it.
Anyone who knows how to do this if it is possible, will you then
please
help
me.

 

[Guest]

I have a slight problem along these lines which is sending me crazy.

I have a form - Frm Query where I have unbound text boxes. I want the data
entered in these boxes to be the criteria for the query.

It works if all of the boxes have things in, BUT i need it to work when only
some are filled and the rest to be considered to be all of the possibilities
(i.e. if status is not completed, then I need the query to return data for
all of the record irrespective of its status)

Please help!

Not a way in a parameter in a query.

Is a way, though. Create a form that you'll use to feed the query the
parameter. Add a combo box to the form, based on the source of your list.
Modify your query to use that form, that combo box as its parameter.
Instead of something like:

[Please Enter Your Parameter]

in the query design, it will look something like:

Forms!YourNewForm!YourComboBox

Now add a command button to the form to run the query. Here's the sequence
of how it would work:

open the form (note: the form MUST be opened and the combo box

selection made for this to work)

select a value from the combo box
click the command button
Access opens the query and notices that it needs a parameter
Access sees that the parameter is pointing at the form's combo box and looks there for a value
Access runs the query

--
Good luck

Jeff Boyce
<Access MVP>

Is ther in the query in Access possible to create a parameter that present
you a list in the parameter box popping up, so you don’t have to write the
criteria but only to choose it.
Anyone who knows how to do this if it is possible, will you then please help
me.