Help: counting number of distinct records in report

[Umbra1]

I'm creating a report for an imaginary "medical clinic's database" as part of a university assignment and need help with a specific formula, the intended function of which is described as follows: "Create a statistic that shows the total number of distinct drugs prescribed to a patient."

Where I'm at:
I've created a query called UniqueDrugs containing drug and patient info. The SQL is:

SELECT DISTINCT Drugs.Drug_Name, Patients.[Patient _ID]
FROM Patients INNER JOIN (Drugs INNER JOIN Prescriptions ON Drugs.[Drug_ID] = Prescriptions.[Drug_ID]) ON Patients.[Patient _ID] = Prescriptions.[Patient_ID]
GROUP BY Drugs.Drug_Name, Patients.[Patient _ID]
ORDER BY Patients.[Patient _ID];

The results of this query seem to be what I need. All I need is for the count formula that I use to return the number of distinct drug names there are that are related to a patient's ID.

I've created a report and I've put the following formula into a text box:

=DCount("[Drug_Name]","[UniqueDrugs]","[Patient_ID]=[PID]")

Where PID is the name of another text box in the report that contains the Patient ID that I want to compare to the drug names returned by the query.

However, this *always* results in #Error, no matter how I change the formula.
I have been led to believe that syntax is not the issue, as the following formula worked as intended for a different task:

=DCount("[Prescription_Date]","[UniqueYearQuery]","[Patient_ID]=[PID]")

So, my question:
How do I use a DCount to return the number of different drugs that have been prescribed to a patient?
If it did not require the number of different drugs, I would simply use the following formula, which works just fine:

=Count(Drug_Name)

Any help would be appreciated and I can provide clarification if needed.

 

[JHB]

Have you tried?

=DCount("[Drug_Name]","[UniqueDrugs]","[Patient_ID]=" & [PID])

Else try to put in a real number like: "[Patient_ID]=10"

 

[Umbra1]

Thanks for the reply.
Unfortunately, neither of those options works. Replacing [PID] with a number and changing the code to your alternative both return #Error.

 

[JHB]

Is it possible to post a stripped version of your database with some sample data, (zip it then you haven't post 10 post yet)?
Plus information which report there has the problem.

 

[Umbra1]

I've attached the database in a zip file. I removed some fields and other reports/forms to cut it down a little.
The report in question is titled Patient Medical History Report and the formula returning #Error should be readily visible.

Thanks very much for the help.

 

[JHB]

You have spaces in some of your field names before the underscore, (spaces in field names is really a bad idea).
Another bad thing is to give a text field a name, which could indicate your are storing a number in the field like "Patient _ID", the ....ID indicate for me it should be a number and not text value.
The below code would give you the result you wanted.

=DCount("[Drug_Name]";"[UniqueDrugs]";"[Patient _ID]='" & [PID] & "'")

 

[Umbra1]

Those spaces were completely unintentional; I have no idea how that happened. :/ Thanks for letting me know!

Actually, fixing those spaces seems to have fixed the issue entirely. It all works now. Thanks -- sorry for wasting your time with an error as silly as a field name mismatch.