Error subquery with date range

[iansan]

I have the following query as data source in a report.

SELECT DISTINCTROW Members.LastName, Members.FirstName, Members.Title, Members.Address, Members.City, Members.PostalCode, Members.AccountNumber, Members.[Medical Fund], Members.MedFundNumber, Members.IDNumber, Charges.ChDate, Charges.[Patient Name], Charges.TransID, Charges.DiagnosisCode, Charges.Diagnosis, Charges.UnitPrice, Charges.Units, Charges.Amount, Charges.MemberPaid, Charges.MedAidPaid, Charges.Total, Charges.Debits, Charges.Credits, Patients2.DOB
FROM (Members INNER JOIN (Charges INNER JOIN Patients2 ON Charges.[Patient Name] = Patients2.FirstName) ON Members.AccountNumber = Patients2.AccountNumber) INNER JOIN Transactions ON (Transactions.TransID = Charges.TransID) AND (Members.AccountNumber = Transactions.AccountNumber)
WHERE (((Members.[Medical Fund]) Like "Protector*&quot AND ((Charges.ChDate) Between [Enter Start Date] And [Enter End Date]) AND ((Charges.Total)>0) AND ((Patients2.DOB)=(SELECT [DOB] From [Patients2] WHERE [Patients2].[FirstName] = [Charges].[Patient Name] AND [Patients2].[AccountNumber] = [Charges].[AccountNumber])));

The user is prompted to enter a date range, then only records from a certain medical fund within that range is printed. It used to work well! However, now, when I enter a date starting in October to a date in November, it returns an error message stating that " at most one record can be returned with this type of subquery"(error 3354). A date range earlier than November returns the neccessary records, as well as when entering the start date only from the 1st of November.
Another query I use, replacing (LIKE "Protector*&quotwith (Not LIKE "Protector*&quot, returns the correct records, irrespective of the date range I enter.
What could be the possible cause?
Thank You

 

[JimHorton]

I'm not sure the structure of your tables, but it looks like you don't need the sub-select for DOB, if you're joining the Patients2 by account# anyway, just use DOB as straight criteria, without the subselect. It appears that depending on other criteria, this subselect can return one row or more than one, which is why it works sometimes and not others.
--Jim

 

[JaneC]

Another possibility is that you have duplicates in the Patient2 table (FirstName and AccountNumber) as the select will only work if it is selecting one row.

JC

 

[iansan]

Thank you all.
I removed the DOB subquery. Problem solved.
Regards
Ian Smook