Combo Box Value question

G

Guest

Hi All,

I have a query that has a field parameter based on a combo box value. The
possible selections in the combo box are "Monday", "Tuesday", "Wednesday",
"Thursday", and "Friday". Is there a way to add another combo box value that
would return all 5 days in the query results? Any help is greatly
appreciated.
Thanks,

ME.
 
G

Guest

Ofer, I have a similar problem, and I think your suggestion would solve it,
but I'm not completely sure where I would put the Select code.



Ofer Cohen said:
If no value will be selected in the combo, then return all the days

Select * From TableName Where FieldName = Forms![FormName]![ComboName] Or
Forms![FormName]![ComboName] Is Null

--
Good Luck
BS"D


Matt said:
Hi All,

I have a query that has a field parameter based on a combo box value. The
possible selections in the combo box are "Monday", "Tuesday", "Wednesday",
"Thursday", and "Friday". Is there a way to add another combo box value that
would return all 5 days in the query results? Any help is greatly
appreciated.
Thanks,

ME.
Click to expand...
Click to expand...
 
G

Guest

I'll try

SELECT [Forms]![Finance & DSS Data Form]![combo Report Group] AS Name,
Sum(Val([PaidAmt])) AS PaidAmount, dbo_EHP_OutpatientVisits.TosDescriptive AS
TOS_Descriptive, dbo_EHP_OutpatientVisits.IncurredMonth,
Sum(Sgn(dbo_EHP_OutpatientVisits!PaidAmt)) AS OPVisits

FROM (Group_Information INNER JOIN Sub_Group_Information ON
Group_Information.GroupID = Sub_Group_Information.GroupID) INNER JOIN
dbo_EHP_OutpatientVisits ON (Sub_Group_Information.GroupID =
dbo_EHP_OutpatientVisits.GroupNbr) AND (Sub_Group_Information.SubGroupID =
dbo_EHP_OutpatientVisits.Subgroup)

WHERE (((dbo_EHP_OutpatientVisits.IncurredMonth)>=200611) AND
((dbo_EHP_OutpatientVisits.PaidMonth)>=200307) AND
((Sub_Group_Information.ReportGroup)=[Forms]![Finance & DSS Data
Form]![combo Report Group] Or [Forms]![Finance & DSS Data Form]![combo
Report Group]) Is Null)) OR
(((dbo_EHP_OutpatientVisits.IncurredMonth)>=200611) AND
((dbo_EHP_OutpatientVisits.PaidMonth)>=200307) AND
((Sub_Group_Information.ReportGroup2)=[Forms]![Finance & DSS Data
Form]![combo Report Group2] Or [Forms]![Finance & DSS Data
Form]![combo Report Group2] Is Null)) OR
(((dbo_EHP_OutpatientVisits.IncurredMonth)>=200611) AND
((dbo_EHP_OutpatientVisits.PaidMonth)>=200307) AND
((Sub_Group_Information.GroupID)=[Forms]![Finance & DSS Data Form]![combo
GroupID] Or [Forms]![Finance & DSS Data Form]![combo
GroupID] Is Null)) OR (((dbo_EHP_OutpatientVisits.IncurredMonth)>=200611) AND
((dbo_EHP_OutpatientVisits.PaidMonth)>=200307) AND
((Sub_Group_Information.SubGroupID)=[Forms]![Finance & DSS Data Form]![combo
SubGroupID] Or [Forms]![Finance & DSS Data Form]![combo SubGroupID] Is Null))
OR (((dbo_EHP_OutpatientVisits.IncurredMonth)>=200611) AND
((dbo_EHP_OutpatientVisits.PaidMonth)>=200307) AND
((Sub_Group_Information.SubGroupName)=[Forms]![Finance & DSS Data
Form]![combo SubGroupName] Or [Forms]![Finance & DSS Data
Form]![combo SubGroupName] Is Null)) OR
(((dbo_EHP_OutpatientVisits.IncurredMonth)>=200611) AND
((dbo_EHP_OutpatientVisits.PaidMonth)>=200307) AND (([Forms]![Finance & DSS
Data Form]![combo Report Group])="ALL"))

GROUP BY [Forms]![Finance & DSS Data Form]![combo Report Group],
dbo_EHP_OutpatientVisits.TosDescriptive,
dbo_EHP_OutpatientVisits.IncurredMonth

HAVING (((Sum(Val([PaidAmt])))<>0));

--
Good Luck
BS"D


shorticake said:
I think I almost have it. For now, I am making it so that a selection is
made from 1 of the 5 comboboxes, and that selection is what filters the query
named Trend OP Paid & Visits. My problem is expanding the SELECT part so
that it displays the criteria selected from 1 of the 5 comboboxes (now I have
5 comboxes not just 1, and combo Report Group will not always be the combobox
used). How can I change my code below to reflect this change?

Thanks so much for your help!


SELECT [Forms]![Finance & DSS Data Form]![combo Report Group] AS Name,
Sum(Val([PaidAmt])) AS PaidAmount, dbo_EHP_OutpatientVisits.TosDescriptive AS
TOS_Descriptive, dbo_EHP_OutpatientVisits.IncurredMonth,
Sum(Sgn(dbo_EHP_OutpatientVisits!PaidAmt)) AS OPVisits

FROM (Group_Information INNER JOIN Sub_Group_Information ON
Group_Information.GroupID = Sub_Group_Information.GroupID) INNER JOIN
dbo_EHP_OutpatientVisits ON (Sub_Group_Information.GroupID =
dbo_EHP_OutpatientVisits.GroupNbr) AND (Sub_Group_Information.SubGroupID =
dbo_EHP_OutpatientVisits.Subgroup)

WHERE (((dbo_EHP_OutpatientVisits.IncurredMonth)>=200611) AND
((dbo_EHP_OutpatientVisits.PaidMonth)>=200307) AND
((Sub_Group_Information.ReportGroup)=[Forms]![Finance & DSS Data Form]![combo
Report Group])) OR (((dbo_EHP_OutpatientVisits.IncurredMonth)>=200611) AND
((dbo_EHP_OutpatientVisits.PaidMonth)>=200307) AND
((Sub_Group_Information.ReportGroup2)=[Forms]![Finance & DSS Data
Form]![combo Report Group2])) OR
(((dbo_EHP_OutpatientVisits.IncurredMonth)>=200611) AND
((dbo_EHP_OutpatientVisits.PaidMonth)>=200307) AND
((Sub_Group_Information.GroupID)=[Forms]![Finance & DSS Data Form]![combo
GroupID])) OR (((dbo_EHP_OutpatientVisits.IncurredMonth)>=200611) AND
((dbo_EHP_OutpatientVisits.PaidMonth)>=200307) AND
((Sub_Group_Information.SubGroupID)=[Forms]![Finance & DSS Data Form]![combo
SubGroupID])) OR (((dbo_EHP_OutpatientVisits.IncurredMonth)>=200611) AND
((dbo_EHP_OutpatientVisits.PaidMonth)>=200307) AND
((Sub_Group_Information.SubGroupName)=[Forms]![Finance & DSS Data
Form]![combo SubGroupName])) OR
(((dbo_EHP_OutpatientVisits.IncurredMonth)>=200611) AND
((dbo_EHP_OutpatientVisits.PaidMonth)>=200307) AND (([Forms]![Finance & DSS
Data Form]![combo Report Group])="ALL"))

GROUP BY [Forms]![Finance & DSS Data Form]![combo Report Group],
dbo_EHP_OutpatientVisits.TosDescriptive,
dbo_EHP_OutpatientVisits.IncurredMonth

HAVING (((Sum(Val([PaidAmt])))<>0));

Ofer Cohen said:
If you have a query that is filtered by using a combo/text box in a form, or
if you have one combo filtered by another combo, the usual SQL will look like

Select * From Where FieldName = Forms![FormName]![ComboName]

But if you want to add to it, that all the records will be dislayed if no
value was selected in the combo then add an "Or" criteria

Select * From Where FieldName = Forms![FormName]![ComboName] Or
Forms![FormName]![ComboName] Is Null


If you need help, post your original SQL with explenation of what you are
trying to do

--
Good Luck
BS"D


shorticake said:
Ofer, I have a similar problem, and I think your suggestion would solve it,
but I'm not completely sure where I would put the Select code.



:

If no value will be selected in the combo, then return all the days

Select * From TableName Where FieldName = Forms![FormName]![ComboName] Or
Forms![FormName]![ComboName] Is Null

--
Good Luck
BS"D


:

Hi All,

I have a query that has a field parameter based on a combo box value. The
possible selections in the combo box are "Monday", "Tuesday", "Wednesday",
"Thursday", and "Friday". Is there a way to add another combo box value that
would return all 5 days in the query results? Any help is greatly
appreciated.
Thanks,

ME.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

If you have a query that is filtered by using a combo/text box in a form, or
if you have one combo filtered by another combo, the usual SQL will look like

Select * From Where FieldName = Forms![FormName]![ComboName]

But if you want to add to it, that all the records will be dislayed if no
value was selected in the combo then add an "Or" criteria

Select * From Where FieldName = Forms![FormName]![ComboName] Or
Forms![FormName]![ComboName] Is Null


If you need help, post your original SQL with explenation of what you are
trying to do

--
Good Luck
BS"D


shorticake said:
Ofer, I have a similar problem, and I think your suggestion would solve it,
but I'm not completely sure where I would put the Select code.



Ofer Cohen said:
If no value will be selected in the combo, then return all the days

Select * From TableName Where FieldName = Forms![FormName]![ComboName] Or
Forms![FormName]![ComboName] Is Null

--
Good Luck
BS"D


Matt said:
Hi All,

I have a query that has a field parameter based on a combo box value. The
possible selections in the combo box are "Monday", "Tuesday", "Wednesday",
"Thursday", and "Friday". Is there a way to add another combo box value that
would return all 5 days in the query results? Any help is greatly
appreciated.
Thanks,

ME.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I think I almost have it. For now, I am making it so that a selection is
made from 1 of the 5 comboboxes, and that selection is what filters the query
named Trend OP Paid & Visits. My problem is expanding the SELECT part so
that it displays the criteria selected from 1 of the 5 comboboxes (now I have
5 comboxes not just 1, and combo Report Group will not always be the combobox
used). How can I change my code below to reflect this change?

Thanks so much for your help!


SELECT [Forms]![Finance & DSS Data Form]![combo Report Group] AS Name,
Sum(Val([PaidAmt])) AS PaidAmount, dbo_EHP_OutpatientVisits.TosDescriptive AS
TOS_Descriptive, dbo_EHP_OutpatientVisits.IncurredMonth,
Sum(Sgn(dbo_EHP_OutpatientVisits!PaidAmt)) AS OPVisits

FROM (Group_Information INNER JOIN Sub_Group_Information ON
Group_Information.GroupID = Sub_Group_Information.GroupID) INNER JOIN
dbo_EHP_OutpatientVisits ON (Sub_Group_Information.GroupID =
dbo_EHP_OutpatientVisits.GroupNbr) AND (Sub_Group_Information.SubGroupID =
dbo_EHP_OutpatientVisits.Subgroup)

WHERE (((dbo_EHP_OutpatientVisits.IncurredMonth)>=200611) AND
((dbo_EHP_OutpatientVisits.PaidMonth)>=200307) AND
((Sub_Group_Information.ReportGroup)=[Forms]![Finance & DSS Data Form]![combo
Report Group])) OR (((dbo_EHP_OutpatientVisits.IncurredMonth)>=200611) AND
((dbo_EHP_OutpatientVisits.PaidMonth)>=200307) AND
((Sub_Group_Information.ReportGroup2)=[Forms]![Finance & DSS Data
Form]![combo Report Group2])) OR
(((dbo_EHP_OutpatientVisits.IncurredMonth)>=200611) AND
((dbo_EHP_OutpatientVisits.PaidMonth)>=200307) AND
((Sub_Group_Information.GroupID)=[Forms]![Finance & DSS Data Form]![combo
GroupID])) OR (((dbo_EHP_OutpatientVisits.IncurredMonth)>=200611) AND
((dbo_EHP_OutpatientVisits.PaidMonth)>=200307) AND
((Sub_Group_Information.SubGroupID)=[Forms]![Finance & DSS Data Form]![combo
SubGroupID])) OR (((dbo_EHP_OutpatientVisits.IncurredMonth)>=200611) AND
((dbo_EHP_OutpatientVisits.PaidMonth)>=200307) AND
((Sub_Group_Information.SubGroupName)=[Forms]![Finance & DSS Data
Form]![combo SubGroupName])) OR
(((dbo_EHP_OutpatientVisits.IncurredMonth)>=200611) AND
((dbo_EHP_OutpatientVisits.PaidMonth)>=200307) AND (([Forms]![Finance & DSS
Data Form]![combo Report Group])="ALL"))

GROUP BY [Forms]![Finance & DSS Data Form]![combo Report Group],
dbo_EHP_OutpatientVisits.TosDescriptive,
dbo_EHP_OutpatientVisits.IncurredMonth

HAVING (((Sum(Val([PaidAmt])))<>0));

Ofer Cohen said:
If you have a query that is filtered by using a combo/text box in a form, or
if you have one combo filtered by another combo, the usual SQL will look like

Select * From Where FieldName = Forms![FormName]![ComboName]

But if you want to add to it, that all the records will be dislayed if no
value was selected in the combo then add an "Or" criteria

Select * From Where FieldName = Forms![FormName]![ComboName] Or
Forms![FormName]![ComboName] Is Null


If you need help, post your original SQL with explenation of what you are
trying to do

--
Good Luck
BS"D


shorticake said:
Ofer, I have a similar problem, and I think your suggestion would solve it,
but I'm not completely sure where I would put the Select code.



Ofer Cohen said:
If no value will be selected in the combo, then return all the days

Select * From TableName Where FieldName = Forms![FormName]![ComboName] Or
Forms![FormName]![ComboName] Is Null

--
Good Luck
BS"D


:

Hi All,

I have a query that has a field parameter based on a combo box value. The
possible selections in the combo box are "Monday", "Tuesday", "Wednesday",
"Thursday", and "Friday". Is there a way to add another combo box value that
would return all 5 days in the query results? Any help is greatly
appreciated.
Thanks,

ME.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Consolidating field values 3
Using combo box(s) to create a query 1
combo box column value in query 7
Query with results in a value list from a combo box? 1
Combo Box in a Query 3
Week and days query 8
Report Generator Question 8
Default Value for Combo Box 1

Top