error message on find duplicate query

[Benj]

I have this error "Your query does not include the specific expression (blah blah blah) as part of an aggregate function" when i did a FIND DUPLICATE QUERY. here it is

SELECT tblpiglet.SowIds, tblpiglet.PigletDate, tblpiglet.PigNum, tblpiglet.Reason, [SowIds] & [PigletDate] & IIf(IsNull([Reason]),IIf([PigNum]>0,"P","N"),[Reason]) AS Filtering FROM tblpiglet WHERE ((([SowIds] & [PigletDate] & IIf(IsNull([Reason]),IIf([PigNum]>0,"P","N"),[Reason])) In (SELECT [Filtering] FROM [qryDupSource] As Tmp GROUP BY [Filtering] HAVING Count(*)>1 ))) ORDER BY tblpiglet.SowIds, tblpiglet.PigletDate, [SowIds] & [PigletDate] & IIf(IsNull([Reason]),IIf([PigNum]>0,"P","N"),[Reason]);




thanks in advance!

[ranman256]

Is this home made ,or made by query wizard.
the wizard don't error.

i would make a regular select query WITH your IIF.
THEN make the dupe query on that.

[Benj]

what i did was make a regular query
SELECT tblpiglet.SowIds, tblpiglet.PigletDate, tblpiglet.PigNum, tblpiglet.Reason, [SowIds] & [PigletDate] & IIf(IsNull([Reason]),IIf([PigNum]>0,"P","N"),[Reason]) AS Filtering FROM tblpiglet ORDER BY tblpiglet.SowIds, tblpiglet.PigletDate, [SowIds] & [PigletDate] & IIf(IsNull([Reason]),IIf([PigNum]>0,"P","N"),[Reason]);


then used this query for the wizard duplicate, it gave me that error. so i just copied the criteria from the duplicate query and paste it on my first query, still the same error

thanks ranman256

[ranman256]

no, you dont copy the criteria, Query 1 has the criteria.
then the Find duplicate query uses Q1.
zero criteria in FDQ.