How to specify a Date Range in a Duplicate Query? 1

[Jrs2nd]

I would like to "filter" or sort the duplicates found by
limiting them to those that are no greater than 30 days old
from the date queried. something like Between Date() And
(Date()- 30).. as you can see (from below) coding is not my
forte'. I wish to do this so that when a new record is about to be entered a (to be coded)warning pops up to say there are dupes of the info about to be input.. ( this
Query is the first stage)next will to call this from the
input form using the about to be entered data as parameters
to use for the "filter".

Below is the SQL for the Find Dupe Query.. and as you can
see in the highlighted section of the SQL code.. My attempt
at acheiving this is (maybe conceptually OK) but in
actuality pretty sad! )

SELECT DISTINCTROW ALLRECORDS.RecordNumber, ALLRECORDS.AssetID, ALLRECORDS.AssetDescription, ALLRECORDS.[Input Date], ALLRECORDS.Supervisor
FROM ALLRECORDS
WHERE (((ALLRECORDS.AssetID) In (SELECT [AssetID] FROM [ALLRECORDS] As Tmp GROUP BY [AssetID],[AssetDescription] HAVING Count(*)>1 And [AssetDescription] = [ALLRECORDS].[AssetDescription])) AND ((([ALLRECORDS].[Input Date])>Date()-30)<&quot;Date&quot)
ORDER BY ALLRECORDS.AssetID, ALLRECORDS.AssetDescription;

Any suggestion would be greatly appreciated.

Jim

 

[THoey]

Shouldn't this be

(([ALLRECORDS].[Input Date] > Date()-30)and ([ALLRECORDS].[Input Date] < DATE()))

or

([ALLRECORDS].[Input Date] BETWEEN Date()-30 AND DATE())

Just a guess... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[Jrs2nd]

Hi Terry,

Thank you, the second option was perfect.
(([ALLRECORDS].[Input Date] BETWEEN Date()-30 AND DATE()))

Once in a while, to be able to think like &quot;SPOCK&quot; would be
nice! Rational I can do, Logical,.....well.. X-)


Jim