S
shorticake
I have a form with several queries set to run by macros. The queries will
run based on the criteria chosen from 1 of the various drop down boxes that
are also located on the form. When I go to run the query I want it to check
the drop down boxes 1 at a time, and move on to the next drop down box if
there is nothing selected. Then when it runs I'd like it to use the name it
used to filter as part of the data provided.
My problem at this time is that when the macro goes to run the query it asks
for me to enter the parameter value. Can you tell what I'm doing wrong and
how to revise it by looking at the SQL I included below?
Thanks so much for your help!
SELECT [Forms]![Form_Main]![Report Group] Or [Forms]![Form_Main]![Report
Group2] Or [Forms]![Form_Main]![Group #] Or [Forms]![Form_Main]![Sub Group #]
Or [Forms]![Form_Main]![Sub Group Name] AS [Group],
dbo_EHP_OutpatientVisits.TosDescriptive AS TOS_Descriptive,
dbo_EHP_OutpatientVisits.IncurredMonth, Sum(Val([PaidAmt])) AS PaidAmount,
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.SubGroupID =
dbo_EHP_OutpatientVisits.Subgroup) AND (Sub_Group_Information.GroupID =
dbo_EHP_OutpatientVisits.GroupNbr)
WHERE (((dbo_EHP_OutpatientVisits.IncurredMonth)>=200307) AND
((dbo_EHP_OutpatientVisits.PaidMonth)>=200307) AND
(([Group])=[Forms]![Form_Main]![Report Group] Or
([Group])=[Forms]![Form_Main]![Report Group2] Or
([Group])=[Forms]![Form_Main]![Group #] Or ([Group])=[Forms]![Form_Main]![Sub
Group #] Or ([Group])=[Forms]![Form_Main]![Sub Group Name])) OR
((([Forms]![Form_Main]![ReportGroup])="ALL"))
GROUP BY dbo_EHP_OutpatientVisits.TosDescriptive,
dbo_EHP_OutpatientVisits.IncurredMonth
HAVING (((Sum(Val([PaidAmt])))<>0));
Here is macro code for 1 of my queries that runs from the form.
Private Sub Outpatient_Paid_and_Visits_Click()
On Error GoTo Err_cmd_Outpatient_Paid_and_Visits_Click
Dim stDocName As String
stDocName = "Trend OP Paid & Visits"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_Outpatient_Paid_and_Visits_Click:
Exit Sub
Err_cmd_Outpatient_Paid_and_Visits_Click:
MsgBox Err.Description
Resume Exit_Outpatient_Paid_and_Visits_Click
End Sub
run based on the criteria chosen from 1 of the various drop down boxes that
are also located on the form. When I go to run the query I want it to check
the drop down boxes 1 at a time, and move on to the next drop down box if
there is nothing selected. Then when it runs I'd like it to use the name it
used to filter as part of the data provided.
My problem at this time is that when the macro goes to run the query it asks
for me to enter the parameter value. Can you tell what I'm doing wrong and
how to revise it by looking at the SQL I included below?
Thanks so much for your help!
SELECT [Forms]![Form_Main]![Report Group] Or [Forms]![Form_Main]![Report
Group2] Or [Forms]![Form_Main]![Group #] Or [Forms]![Form_Main]![Sub Group #]
Or [Forms]![Form_Main]![Sub Group Name] AS [Group],
dbo_EHP_OutpatientVisits.TosDescriptive AS TOS_Descriptive,
dbo_EHP_OutpatientVisits.IncurredMonth, Sum(Val([PaidAmt])) AS PaidAmount,
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.SubGroupID =
dbo_EHP_OutpatientVisits.Subgroup) AND (Sub_Group_Information.GroupID =
dbo_EHP_OutpatientVisits.GroupNbr)
WHERE (((dbo_EHP_OutpatientVisits.IncurredMonth)>=200307) AND
((dbo_EHP_OutpatientVisits.PaidMonth)>=200307) AND
(([Group])=[Forms]![Form_Main]![Report Group] Or
([Group])=[Forms]![Form_Main]![Report Group2] Or
([Group])=[Forms]![Form_Main]![Group #] Or ([Group])=[Forms]![Form_Main]![Sub
Group #] Or ([Group])=[Forms]![Form_Main]![Sub Group Name])) OR
((([Forms]![Form_Main]![ReportGroup])="ALL"))
GROUP BY dbo_EHP_OutpatientVisits.TosDescriptive,
dbo_EHP_OutpatientVisits.IncurredMonth
HAVING (((Sum(Val([PaidAmt])))<>0));
Here is macro code for 1 of my queries that runs from the form.
Private Sub Outpatient_Paid_and_Visits_Click()
On Error GoTo Err_cmd_Outpatient_Paid_and_Visits_Click
Dim stDocName As String
stDocName = "Trend OP Paid & Visits"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_Outpatient_Paid_and_Visits_Click:
Exit Sub
Err_cmd_Outpatient_Paid_and_Visits_Click:
MsgBox Err.Description
Resume Exit_Outpatient_Paid_and_Visits_Click
End Sub