Hi
Stand alone query is fine but if I try to Apply Filter to a form it asks for parameters.
Have query below (prob to complex to see) but is basically a Find Duplicates query, mine finds if overlapping booking dates also.
Typical find duplicate query has the same table twice with join.
If I just run the query it is perfect, lists all the duplicates I want.
I was hoping to filter a form on this data, exact same data that is on the form, I have unticked any Dogs_1(2nd copy table) fields but if I use docmd.apply filter is asks for parameters for all the Dogs_1 fields, RegID, Indate, outdate etc.
SELECT DISTINCTROW Dogs.Key, Dogs.Names, Dogs.Indate, Dogs.OutDate, Dogs.Code, Dogs.Comment, Dogs.RegBreed, Dogs.MessageID, Dogs.RegID
FROM Dogs INNER JOIN Dogs AS Dogs_1 ON Dogs.RegID = Dogs_1.RegID
WHERE (((Dogs.RegID)<>IsNull([Dogs].[RegID])) AND ((Dogs_1.RegID)<>IsNull([Dogs_1].[RegID])) AND ((Nz(([Dogs_1].[Indate]>=[Dogs].[OutDate]) Or ([Dogs_1].[OutDate]<=[Dogs].[Indate]) Or ([Dogs].[RegID]<>[Dogs_1].[RegID]) Or ([Dogs].[ID]=[Dogs_1].[ID]),False))=False))
ORDER BY Dogs.RegID;
Perhaps something I can't do but actually can't think of a reason why it would break logic & be impossible.
Thanks I/A
Stand alone query is fine but if I try to Apply Filter to a form it asks for parameters.
Have query below (prob to complex to see) but is basically a Find Duplicates query, mine finds if overlapping booking dates also.
Typical find duplicate query has the same table twice with join.
If I just run the query it is perfect, lists all the duplicates I want.
I was hoping to filter a form on this data, exact same data that is on the form, I have unticked any Dogs_1(2nd copy table) fields but if I use docmd.apply filter is asks for parameters for all the Dogs_1 fields, RegID, Indate, outdate etc.
SELECT DISTINCTROW Dogs.Key, Dogs.Names, Dogs.Indate, Dogs.OutDate, Dogs.Code, Dogs.Comment, Dogs.RegBreed, Dogs.MessageID, Dogs.RegID
FROM Dogs INNER JOIN Dogs AS Dogs_1 ON Dogs.RegID = Dogs_1.RegID
WHERE (((Dogs.RegID)<>IsNull([Dogs].[RegID])) AND ((Dogs_1.RegID)<>IsNull([Dogs_1].[RegID])) AND ((Nz(([Dogs_1].[Indate]>=[Dogs].[OutDate]) Or ([Dogs_1].[OutDate]<=[Dogs].[Indate]) Or ([Dogs].[RegID]<>[Dogs_1].[RegID]) Or ([Dogs].[ID]=[Dogs_1].[ID]),False))=False))
ORDER BY Dogs.RegID;
Perhaps something I can't do but actually can't think of a reason why it would break logic & be impossible.
Thanks I/A