How to use a query on report

[Guest]

I have a query that has a condition [Site] where the person is suppose to
enter a site number. When I enter a number in the text box the pop up box
keeps coming up asking for [Site]. Here is the information I am using:

For opening the report
Dim stDocName As String
stDocName = "SiteTotals"

Dim strWhere As String
strWhere = "NURSE = '" & Me!Site & "'"

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Query:
SELECT [~TotalCompleted1].TotalCompleted1, [~MMSE15].GreaterThan15,
[~MMSE16].LessThan16, [~TotalEligible2].CountOfELIGIBLE,
[~NotEligible2].NotEligible2, [~NotEligibleAD2].NotEligibleAD2,
[~NotEligibleMMSE].NotEligibleMMSE, [~TotalCompleted2].Phase2TotalCompleted,
[~EligibleRefused2].EligibleRefused2, [~TotalPending2].TotalPending2,
[~TotalEligible3].TotalEligible3, [~NotEligible3].TotalNotEligible3,
[~NotEligibleGDS3].NotEligibleGDS3, [~TotalCompleted3].Phase3TotalCompleted,
[~EligibleRefused3].EligibleRefused3, [~TotalPending3].TotalPending3,
[~TotalEligible4].TotalEligible4, [~NotEligible4].NotEligible4,
[~EligibleRefused4].EligibleRefused4,
[~BaselinesCompleted4].BaselinesCompleted, [~TotalPending4].TotalPending4
FROM [~TotalCompleted1], [~MMSE15], [~MMSE16], [~TotalEligible2],
[~NotEligible2], [~NotEligibleAD2], [~NotEligibleMMSE], [~TotalCompleted2],
[~EligibleRefused2], [~TotalPending2], [~TotalEligible3], [~NotEligible3],
[~NotEligibleGDS3], [~TotalCompleted3], [~EligibleRefused3],
[~TotalPending3], [~TotalEligible4], [~NotEligible4], [~EligibleRefused4],
[~BaselinesCompleted4], [~TotalPending4], screen
WHERE (((screen.NURSE)=[Site]))
GROUP BY [~TotalCompleted1].TotalCompleted1, [~MMSE15].GreaterThan15,
[~MMSE16].LessThan16, [~TotalEligible2].CountOfELIGIBLE,
[~NotEligible2].NotEligible2, [~NotEligibleAD2].NotEligibleAD2,
[~NotEligibleMMSE].NotEligibleMMSE, [~TotalCompleted2].Phase2TotalCompleted,
[~EligibleRefused2].EligibleRefused2, [~TotalPending2].TotalPending2,
[~TotalEligible3].TotalEligible3, [~NotEligible3].TotalNotEligible3,
[~NotEligibleGDS3].NotEligibleGDS3, [~TotalCompleted3].Phase3TotalCompleted,
[~EligibleRefused3].EligibleRefused3, [~TotalPending3].TotalPending3,
[~TotalEligible4].TotalEligible4, [~NotEligible4].NotEligible4,
[~EligibleRefused4].EligibleRefused4,
[~BaselinesCompleted4].BaselinesCompleted, [~TotalPending4].TotalPending4;

 

[John Vinson]

I have a query that has a condition [Site] where the person is suppose to
enter a site number. When I enter a number in the text box the pop up box
keeps coming up asking for [Site]. Here is the information I am using:

For opening the report
Dim stDocName As String
stDocName = "SiteTotals"

Dim strWhere As String
strWhere = "NURSE = '" & Me!Site & "'"

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

You're trying to do it with belt and suspenders. The WhereCondition in
the OpenReport method is all you need; remove the criterion in the
Query. That's what's giving you the additional prompt.

Alternatively, remove the WhereCondition (just leave strWhere out),
and change the criterion on the query to

=[Forms]![yourformnamehere]![Site]

John W. Vinson[MVP]