Working with dates in reports

  • Thread starter Thread starter Stephen Brown
  • Start date Start date
S

Stephen Brown

I have a field in my database that stores the "date of a
survey" and there is a requirement that the survey be
carried out every 12 months.

In my report I ideally want to examine those records with
a currently valid survey (ie less than 1 year old) and
then those that are less than 1 month, 3 months, 6 months
overdue.

However, the only way I've been able to do it at the
moment is to use the DATEDIFF function to calculate the
different between the current date and the date in the
field, I then count those records where the date
difference is less than 365, between 365 and 395, 395 and
425 and between 425 and 545 (which effectively counts
those current, less than 30, 90 and 180 days overdue.
The added problem with this method is that it doesn't
allow for leap years.

Is there a better way of formatting the query to achieve
exactly what I want?

Many thanks


Stephen Brown
 
Stephen said:
I have a field in my database that stores the "date of a
survey" and there is a requirement that the survey be
carried out every 12 months.

In my report I ideally want to examine those records with
a currently valid survey (ie less than 1 year old) and
then those that are less than 1 month, 3 months, 6 months
overdue.

However, the only way I've been able to do it at the
moment is to use the DATEDIFF function to calculate the
different between the current date and the date in the
field, I then count those records where the date
difference is less than 365, between 365 and 395, 395 and
425 and between 425 and 545 (which effectively counts
those current, less than 30, 90 and 180 days overdue.
The added problem with this method is that it doesn't
allow for leap years.
Click to expand...

Instead of using
DateDiff("d", olddate, newdate) <= 365
try using:
DateAdd("m", 12, olddate) <= newdate
 
I would personally just setup the query with the parameter

Between [Enter Start Date] & [Enter End Date]

This would return the records between the dates you input.

Let me know,
_Bigred
 
Back
Top