multiple master/child links causing 'expression typed incorrectly' (1 Viewer)

Hi All,

Totally stumped on this - had the report working but now.....don't know what's changed.........

So a fairly simple report with two subreports - both subreports have their own queries not relating/using anything from the mainreport. One sub report links fine on two master/child fields ie master Vintage;EmpID and Child Vintage;EmpID

The other subreport is quite similar and uses the same tables but not the same query and will NOT link on two master/child fields - it will link on each one separately but not the two together as the other subreport does........using both link fields i get the 'Expression typed incorrectly or too complex' message. I stripped report down and still same result...

FYI have compacted, reimported into clean db, decompiled/recompiled, created new report, done all the things I can think of, checked and triple checked. Only things I can think of is the subreport 'vintage' field is calculated but why would the other subreport work if this was the problem- it has same calculated field........at a loss

Any thoughts for googling, I've run out of ideas....thank you

Rachael

What are the differences between the two subreport RecordSource queries?

Basically the Link fields are used to requery the Recordsource with the Child Link Fields and the Master Link Fields values added as extra criteria. Maybe it is making that query too complex?

Hi Glaxiom, thanks for your reply!

Here are the two queries:

Report that will only link on either EmpID or Vint but not both at same time based on this query:

SELECT CoNotes.EmpID, CoSubCategory.WineItem, Count(Format([NoteDate],"mm")) AS Mnth, CLng(IIf(Format([NoteDate],"mm")>6,Format([NoteDate],"yyyy")+1,Format([NoteDate],"yyyy"))) AS Vint, CoNotes.NoteDate
FROM (CoSubCategory RIGHT JOIN Company1 ON CoSubCategory.SubCatID = Company1.CategoryID) INNER JOIN CoNotes ON Company1.CoID = CoNotes.CoID
GROUP BY CoNotes.EmpID, CoSubCategory.WineItem, CLng(IIf(Format([NoteDate],"mm")>6,Format([NoteDate],"yyyy")+1,Format([NoteDate],"yyyy"))), CoNotes.NoteDate
HAVING ((Not (CoNotes.EmpID) Is Null) AND ((CoSubCategory.WineItem)=True) AND (Not (CoNotes.NoteDate) Is Null)) OR (((CoNotes.EmpID)=0) AND ((CoSubCategory.WineItem)=True) AND (Not (CoNotes.NoteDate) Is Null));



and second report that works fine linked happily on both EmpID and Vint:
(second query based on the first, looks more complex but works a treat)

(first query called WineMenuContact3mQry)
SELECT Company1.EmpID, CoSubCategory.WineItem, CoNotes.NoteDate, Company1.[Co Name], CLng(IIf(Format([NoteDate],"mm")>6,Format([NoteDate],"yyyy")+1,Format([NoteDate],"yyyy"))) AS Vint, CoSubCategory.SubCat, CoSubCategory.SubCatID
FROM (CoSubCategory RIGHT JOIN Company1 ON CoSubCategory.SubCatID = Company1.CategoryID) INNER JOIN CoNotes ON Company1.CoID = CoNotes.CoID
GROUP BY Company1.EmpID, CoSubCategory.WineItem, CoNotes.NoteDate, Company1.[Co Name], CLng(IIf(Format([NoteDate],"mm")>6,Format([NoteDate],"yyyy")+1,Format([NoteDate],"yyyy"))), CoSubCategory.SubCat, CoSubCategory.SubCatID
HAVING (((CoSubCategory.WineItem)=True) AND (Not (CoNotes.NoteDate) Is Null));

(second query based on the above and report recordsource)
SELECT WineMenuContact3mQry.EmpID, Max(WineMenuContact3mQry.NoteDate) AS MaxOfNoteDate, WineMenuContact3mQry.[Co Name], WineMenuContact3mQry.Vint, WineMenuContact3mQry.SubCatID
FROM WineMenuContact3mQry
GROUP BY WineMenuContact3mQry.EmpID, WineMenuContact3mQry.[Co Name], WineMenuContact3mQry.Vint, WineMenuContact3mQry.SubCatID
HAVING ((Not (WineMenuContact3mQry.EmpID) Is Null) AND ((Max(WineMenuContact3mQry.NoteDate))<Date()-90) AND ((WineMenuContact3mQry.SubCatID) In (13,203)));

Got me stumped!!!

Thanks in advance, kind regards, Rachael

Whether they contribute to the problem I don't know, but you have a few issues with that problem query. Changing them will definitely simplify the query.

There is no point deriving a term just to count on. I suspect you actually want to derive the month and the Count should be a different field. Use Count(*) to count all records by Group.

Format returns a string. Then you are comparing a string to a number so the string is being implicitly cast to a number before making the comparison.

Similarly with:
Use the Month and Year functions to return the numbers from the date directly.

HAVING selects after grouping so you are grouping on everything then returning only those groups with satisfy HAVING. Instead you should eliminate the unwanted records with a WHERE which is applied before the grouping. If you are using design View note that Where is one of the Grouping selections. You can group on one instance of the field and WHERE on another.

Use:
rather than

Thanks Galaxiom for taking the time to reply, I appreciate it (and sorry for taking so long to reply - mid winter and the vineyard needs pruning!!!!) Will take on all your suggestions and have another go - you learn new stuff everyday

Update - changing the queries to use month and year function made all the difference....plenty of ways to skin a cat but some work and some don't lol

Thanks