Query Criteria

[StacyM]

I have a report that is based on a query of two tables. It works great except
I would like to include some criteria that make the report more useful. The
criteria needs to be on the field "position" where there are four choices
from a drop down list. I would like the user to be able to type in into a
parameter box which positions they want to see and the report will be built
accordingly. What I am struggling with is making all the records visible
without having the user type in all four positions, it would be much easier
if they could type "all" and have everything show on the report. I am very
new to access, and am teaching myself as I go. Thank you for sharing your
knowledge.

 

[Duane Hookom]

Could you please share your current query SQL view? IMO, it isn't appropriate
to use parameter prompts. You should do all user interaction through controls
on forms.

 

[StacyM]

SELECT [Contract Information].Position, [Contract Information].Commodity,
[Contract Information].[Settling Year], [Contract Information].[Settling
Month], [Contract Information].[Gain/loss], [Contract Information].Net,
Account.Firm, [Contract Information].[Contract Value]
FROM Account INNER JOIN [Contract Information] ON Account.ID = [Contract
Information].[Account Number];


I am doing all interaction through controls on forms, I apologize for any
confusion. I know I need something in the Where clause, I am just not sure
what or how to write it. Thanks again.

 

[Duane Hookom]

I would use a multi-select list box to allow the user to select the
positions. The query would then depend on if this query is the Record Source
of a report or form or not.

--
Duane Hookom
Microsoft Access MVP

SELECT [Contract Information].Position, [Contract Information].Commodity,
[Contract Information].[Settling Year], [Contract Information].[Settling
Month], [Contract Information].[Gain/loss], [Contract Information].Net,
Account.Firm, [Contract Information].[Contract Value]
FROM Account INNER JOIN [Contract Information] ON Account.ID = [Contract
Information].[Account Number];


I am doing all interaction through controls on forms, I apologize for any
confusion. I know I need something in the Where clause, I am just not sure
what or how to write it. Thanks again.

Could you please share your current query SQL view? IMO, it isn't appropriate
to use parameter prompts. You should do all user interaction through controls
on forms.

 

[StacyM]

I think I need to rephrase my question. I want the user to be able to
navigate to the report, then enter which positions they would like to see
into parameter boxes. Each record can contain one and only one position, but
it can be any one of four. I am looking for a simpler way than making the
user go through four parameter boxes, so if there is a way to make a
parameter box a multi-select box, that would be neat, or a way to write the
SQL so the user could type "Puts and Calls" and see puts and calls, that
would be good also. Sorry for the confusion and thank you for your help!

I would use a multi-select list box to allow the user to select the
positions. The query would then depend on if this query is the Record Source
of a report or form or not.

--
Duane Hookom
Microsoft Access MVP

SELECT [Contract Information].Position, [Contract Information].Commodity,
[Contract Information].[Settling Year], [Contract Information].[Settling
Month], [Contract Information].[Gain/loss], [Contract Information].Net,
Account.Firm, [Contract Information].[Contract Value]
FROM Account INNER JOIN [Contract Information] ON Account.ID = [Contract
Information].[Account Number];


I am doing all interaction through controls on forms, I apologize for any
confusion. I know I need something in the Where clause, I am just not sure
what or how to write it. Thanks again.

Could you please share your current query SQL view? IMO, it isn't appropriate
to use parameter prompts. You should do all user interaction through controls
on forms.

--
Duane Hookom
Microsoft Access MVP


:

I have a report that is based on a query of two tables. It works great except
I would like to include some criteria that make the report more useful. The
criteria needs to be on the field "position" where there are four choices
from a drop down list. I would like the user to be able to type in into a
parameter box which positions they want to see and the report will be built
accordingly. What I am struggling with is making all the records visible
without having the user type in all four positions, it would be much easier
if they could type "all" and have everything show on the report. I am very
new to access, and am teaching myself as I go. Thank you for sharing your
knowledge.

 

[Duane Hookom]

IMO, the only method for getting user input criteria is through controls on
forms. If you want multi-select capabilities, then it becomes difficult and
kludgy to use parameter prompts. Using a multi-select list box requires some
code but there are lots of resources on the web.

--
Duane Hookom
Microsoft Access MVP

I think I need to rephrase my question. I want the user to be able to
navigate to the report, then enter which positions they would like to see
into parameter boxes. Each record can contain one and only one position, but
it can be any one of four. I am looking for a simpler way than making the
user go through four parameter boxes, so if there is a way to make a
parameter box a multi-select box, that would be neat, or a way to write the
SQL so the user could type "Puts and Calls" and see puts and calls, that
would be good also. Sorry for the confusion and thank you for your help!

I would use a multi-select list box to allow the user to select the
positions. The query would then depend on if this query is the Record Source
of a report or form or not.

--
Duane Hookom
Microsoft Access MVP

SELECT [Contract Information].Position, [Contract Information].Commodity,
[Contract Information].[Settling Year], [Contract Information].[Settling
Month], [Contract Information].[Gain/loss], [Contract Information].Net,
Account.Firm, [Contract Information].[Contract Value]
FROM Account INNER JOIN [Contract Information] ON Account.ID = [Contract
Information].[Account Number];


I am doing all interaction through controls on forms, I apologize for any
confusion. I know I need something in the Where clause, I am just not sure
what or how to write it. Thanks again.

:

Could you please share your current query SQL view? IMO, it isn't appropriate
to use parameter prompts. You should do all user interaction through controls
on forms.

--
Duane Hookom
Microsoft Access MVP


:

I have a report that is based on a query of two tables. It works great except
I would like to include some criteria that make the report more useful. The
criteria needs to be on the field "position" where there are four choices
from a drop down list. I would like the user to be able to type in into a
parameter box which positions they want to see and the report will be built
accordingly. What I am struggling with is making all the records visible
without having the user type in all four positions, it would be much easier
if they could type "all" and have everything show on the report. I am very
new to access, and am teaching myself as I go. Thank you for sharing your
knowledge.