Find Duplicate Query asking for parameters for Form (1 Viewer)

[bignose2]

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

 

[isladogs]

As you say it is difficult to read but its also not clear how its a find dupes query.
If you use the find dupes wizard you only need one copy of the table and it searches for selected fields with a count >1 as a subquery.
What exactly is yours doing?
Also what is the form called as its not apparently used in the query

 

[theDBguy]

Hi. Your query may be finding duplicates, but I don't think it's a typical find duplicates query. The typical ones would use a Totals query.

 

[bignose2]

Hi,

Sorry you are right, I had gone back to this issue without thinking it through but seem to remember there is a standard query format that uses a 2nd copy of the same table to achieve something similar.

I am trying to find out why it is asking for parameter or somehow stop it. It does not ask when run just as a query but if I try DoCmd.ApplyFilter "Find Duplicates For Dogs Overlap" on the Form.
This form is normally used for listing, searching or filtering on simple critera and based on a simple query/table structure. I just wanted to apply this filter on top, not really change the record source & think I would have the same problem anyway.

Mine uses the two copies of the same table joined by the unique clientID(RegID).

To find all bookings by the same client that have overlapping dates, i.e. booked twice by mistake.

FIELD: 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)
CRITERIA: False

So hides all records records that do not overlap.

Key Names Indate OutDate Code RegID
ALEXANDER/B BELLA 02-06-2019 14-06-2019 B -1980432057
ALEXANDER/B BELLA 01-06-2019 16-06-2019 C -1980432057
LAW/CP CLYRO & PATRICK 06-06-2019 10-06-2019 1 -1465592498
LAW/CP CLYRO & PATRICK 06-06-2019 10-06-2019 1 -1465592498
ADAMS/N NOODLE 13-08-2019 28-08-2019 C -1128680194
ADAMS/N NOODLE 15-08-2019 30-08-2019 D -1128680194
RICHARDSON/BO BORIS & OTTO 29-06-2019 30-06-2019 1 -286094233
RICHARDSON/BO BORIS & OTTO 24-06-2019 04-07-2019 1 -286094233
MAGEE/T TOBY 28-07-2019 31-07-2019 D 928323465
MAGEE/T TOBY 26-07-2019 29-07-2019 C 928323465
RENDALL/HB HARLEY & BUDDY 03-08-2018 12-08-2018 C 1490524245
RENDALL/HB HARLEY & BUDDY 27-07-2018 05-08-2018 C 1490524245
LOCKING/P POPPY 08-09-2018 18-09-2018 C 2146900377
LOCKING/P POPPY 11-09-2018 13-09-2018 1 2146900377
COLLINS/T TIGGY 29-08-2019 03-09-2019 B 2146900507
COLLINS/T TIGGY 13-08-2019 03-09-2019 C 2146900507
HALL/FC ALFIE 02-05-2018 05-05-2018 D 2146900735
HALL/FC FRITZ & CASPER 28-04-2018 05-05-2018 1 2146900735

 

[bignose2]

SOLVED.

Whilst typing got me thinking,
I tried changing the recordsource to the Query by VBA & it worked

Not sure why applyfilter query does not but not going to waste any more time.

thanks anyway, I really should have thought of that early on.

 

[theDBguy]

SOLVED.

Whilst typing got me thinking,
I tried changing the recordsource to the Query by VBA & it worked

Not sure why applyfilter query does not but not going to waste any more time.

thanks anyway, I really should have thought of that early on.

Hi. Glad to hear you got it sorted out. Good luck with your project.