Displaying Query results on form

[NRP]

Hello - This Board is always so helpful so I will get right to the point. I am building a database in MSAccess 2003 for physcians to track cancer patients they see for Follow Up visits. I have a query that has a Count column which gives me the number of Diagnoses the patient has. When I open the Follow Up form, I run the query On Load, and have a text box that I set the Control Source to equal the Count column in the query

=[qryProstateDiagnosis]![CountProstate]

Unfortunately, the query runs but the text box gives the me the #Name? error.

I was searching the Board for help and came across a thread on "Showing Query Results on a Form" (posted April 2, 2004). I seem to be folowing "Sydney Geek's" advice. See below-
_________________________________________
OK, let's say you have MyQuery with SomeResult. You want SomeResult to show on the form -- and SomeResult is the only item in the query (a typical, single-item Totals query).

Try this...

In the form, place an unbound Textbox. The ControlSource should be
=[MyQuery]![SomeResult]
_____________________________________________

The only way my query might conflict with the above advice is that my CountProstate column is not the only item in the query (my query also has PatientID, DiagnosisCode) BUT, the text box is only tied to the Count column so I'm not sure why the Count number isn't displaying?

Any help is greatly appreciated! ray:

 

[mdmilner]

OMG I can't believe you just asked...

Ok, just kidding. Welcome to the board. I'm not testing as we speak but I believe you've nailed the problem precisely. Sydney was extraordinarily explicit about describing the query as Single Item.

I'm not certain this is a fix, but have you tried using SQL as the ControlSource/RecordSource instead of a saved query?

Mike

 

[NRP]

Thanks for the suggestion. I just tried using the SQL instead of a saved query and still no luck. Here's my syntax for your review:

SELECT Count(*) AS CountProstate
FROM tblPrmryDiagnosis
WHERE (((tblPrmryDiagnosis.PatientID)=[Forms]![frmPatientInformation].[PatientID]) AND ((tblPrmryDiagnosis.PrmryDiagnosisCode)="185"));


I put the Control Source = to the statement above (and added parentheses per the prompt) and still get the #Name? error. I'm stumped!