Keep only records where days between dates > 30 1

[laina222]

I need help with a query. I suspect I need to use the DateDiff function, but don't know much about using it.

I have two tables. The first field of both tables, Agreement, should be present in both tables. On each table there is a different date associated with the Agreement, different on each table. I want to pull the Agreement, some other fields and both dates in my query, but I only want the records where the two dates have 31 or more days between them.

Here's what I wrote, but I get an error message:
SELECT qryReratesWithOptionsAfter2000.*, tblNewBusiness.CycleDate
FROM tblNewBusiness, qryReratesWithOptionsAfter2000
WHERE tblNewBusiness.Agreement = qryReratesWithOptionsAfter2000.Agreement AND DateDiff("31",[tblNewBusiness].[CycleDate], [qryReratesWithOptionsAfter2000].[CycleDate]);

Is my syntax wrong? Any ideas on how to do this differently, perhaps?

 

[lespaul]

something like this?

SELECT qryReratesWithOptionsAfter2000.*, tblNewBusiness.CycleDate
FROM tblNewBusiness, qryReratesWithOptionsAfter2000
WHERE tblNewBusiness.Agreement = qryReratesWithOptionsAfter2000.Agreement AND DateDiff(d,[tblNewBusiness].[CycleDate], [qryReratesWithOptionsAfter2000].[CycleDate]) > 31;

Leslie

 

[laina222]

It did ask me to enter the parameter 'd'...can't remove that part, though, can I??? To run it, I tried "31"
Also, when I ran it I got another error message that said something like "The expression is typed incorrectly (I copied and pasted, so it can't be!) or it is too complex to be evaluated..." When I read your SQL, I thought it would work!

 

[lespaul]

my bad:

SELECT qryReratesWithOptionsAfter2000.*, tblNewBusiness.CycleDate
FROM tblNewBusiness, qryReratesWithOptionsAfter2000
WHERE tblNewBusiness.Agreement = qryReratesWithOptionsAfter2000.Agreement AND DateDiff('d',[tblNewBusiness].[CycleDate], [qryReratesWithOptionsAfter2000].[CycleDate]) > 31;

need the ' around the d

are both CycleDate fields DATE fields?

Leslie

 

[laina222]

It worked! Thanks so much =)
And yes, they are both date fields.

 

[lespaul]

glad to help!!! thanks for the star!

leslie