Why is my query is too complex

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that was runing fine and still is. However, all of a sudden I
can not get into the QBE view, I get the message "Query too complex". I still
can get into the SQL view and the query still runs ok. why am I getting this
message?
 
by the way here is my sql:

SELECT Dosage_BwtShifting_2.PatientID, Dosage_BwtShifting_2.MedicationType1
AS MedType, Dosage_BwtShifting_2.PDate, Dosage_BwtShifting_2.DateofExam,
Dosage_BwtShifting_2.BdyWt_CO, Dosage_BwtShifting_2.BWT,
Dosage_BwtShifting_2.MedicationVol, Dosage_BwtShifting_2.DaysOnMed,
Dosage_BwtShifting_2.Discontinued, Dosage_BwtShifting_3.OmittDate,
Dosage_BwtShifting_2.MedicationFrequency, Dosage_BwtShifting_2.Concentration,
IIf([Dosage_BwtShifting_2].[Discontinued]="DC",[Dosage_BwtShifting_2].[DateofExam]) AS DCDate
FROM Dosage_BwtShifting_3 RIGHT JOIN Dosage_BwtShifting_2 ON
Dosage_BwtShifting_3.OffDate = Dosage_BwtShifting_2.PDate
WHERE (((Dosage_BwtShifting_3.OmittDate) Is Null));
Al
 
"Too complex" just means there is something Access is not able to interpret.

The most likely culprit is the IIf() where no explicit value is for the
False part. Try:
CVDate( IIf([Dosage_BwtShifting_2].[Discontinued]="DC",
[Dosage_BwtShifting_2].[DateofExam], Null)) AS DCDate

Alternatively, if this query is based on other queries, and the fields in
the JOIN are calculated fieldds, Access could have a problem there.

Or if OmittDate is a Yes/No field, Access will probably spit the dummy.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Al said:
by the way here is my sql:

SELECT Dosage_BwtShifting_2.PatientID,
Dosage_BwtShifting_2.MedicationType1
AS MedType, Dosage_BwtShifting_2.PDate, Dosage_BwtShifting_2.DateofExam,
Dosage_BwtShifting_2.BdyWt_CO, Dosage_BwtShifting_2.BWT,
Dosage_BwtShifting_2.MedicationVol, Dosage_BwtShifting_2.DaysOnMed,
Dosage_BwtShifting_2.Discontinued, Dosage_BwtShifting_3.OmittDate,
Dosage_BwtShifting_2.MedicationFrequency,
Dosage_BwtShifting_2.Concentration,
IIf([Dosage_BwtShifting_2].[Discontinued]="DC",[Dosage_BwtShifting_2].[DateofExam])
AS DCDate
FROM Dosage_BwtShifting_3 RIGHT JOIN Dosage_BwtShifting_2 ON
Dosage_BwtShifting_3.OffDate = Dosage_BwtShifting_2.PDate
WHERE (((Dosage_BwtShifting_3.OmittDate) Is Null));
Al

Al said:
I have a query that was runing fine and still is. However, all of a
sudden I
can not get into the QBE view, I get the message "Query too complex". I
still
can get into the SQL view and the query still runs ok. why am I getting
this
message?
Click to expand...
Click to expand...
 
If it used to work and doesn't anymore, then it's probably because of the
data itself. The query is encountering an incorrect, unexpected, or non-
existent data entry that is throwing it off. Check all of your conditions,
including joins, and change the query or the data. So for example, you might
be trying to create a join on a recently created Null entry. Either use the
function Nz() in your query or fix the data in the table. HTH
 
Back
Top