[X]My Assistant

[mike60smart]

Hi Everyone

How can I only show records with a Renewal date within 6 Months of Today's Date ??

Any help appreciated

[RJD]

Hi Mike: I would think a WHERE clause something like ...

WHERE Renewal_date <= DateAdd("m",6,Date())

(untested)

I am assuming you are looking forward to future renewal dates, but also grabbing any past due renewal dates.

If you want ONLY renewals due from today to 6 months out, then perhaps ...

WHERE Renewal_date Between Date() and DateAdd("m",6,Date())

(again, untested)

HTH
Joe

[mike60smart]

Hi Joe

I am trying this in a query:-

Reminders: [Renewal Date] Between Date() And DateAdd("m",6,Date())

It is returning a "-1" ??

Is this because "Renewal Date" is calculated as follows in the query:-

Renewal Date: DateAdd("yyyy",[RenewalPeriod],[TrainingDate])

[theDBguy]

Hi Mike,

When you do this:

CODE

Reminders: [Renewal Date] Between Date() And DateAdd("m",6,Date())

You're basically creating a calculated column asking if the [Renewal Date] is Between today and 6 months from now, which would be either a Yes or a No. So, some records in that column should display a -1 and some should display a 0.

There is nothing wrong with this, and if you want to filter the query to those records where renewal dates are coming up, then you just add a criteria to the Reminders column of -1.

[RJD]

Hi Mike: The Between statement should be in the criteria, in the [Renewal Date] field (column) in Design View.

Between Date() and DateAdd("m",6,Date())

Try that and see if it works for you.

As theDBguy says, the -1 just means True.

HTH
Joe

[mike60smart]

Hi theDBGuy and Joe

theDBGuy many thanks for the explanation. I was puzzled as it would not let me put a criteria of -1

Joe - Perfect that is exactly what I need

Many thanks yet again

[RJD]

You are very welcome, Mike, from both of us.

Regards,
Joe