Date formula help - Date's > than 5 years old!! 1

[tonymerseyfire]

Hi,

I am trying to query a Date Field, which refers to a Last Visit Date carried out by a Fire Crew at a premise, the format of the date field is 29/07/2009.

Most records will have a Last Visit Date, but some will not and contain a “-“, which is the character used for a blank date value in the database.

I would like any help to find out how many records there are in the database that have a Date that is greater than 5 years old from the current date; so firstly I need a formula to identify those records with a Last Visit Date greater than 5 years old, and then be able to insert a count on how many of those records there are greater than 5 years old into the reports footer.

The report is dynamic as it will be changing daily with new records being added, and the report will not be ran just once it will be ran on an ad hoc weekly basis so a formula that can calculate the 5 year difference from the current date, rather than having to enter the current date each time the report is ran would be ideal.

Any help much appreciated!

Thanks.

 

[IanWaterman]

Assume that the field is not a datefield but a string field since it can hold "-".

If it was datetime string you could use the crystal function dtstodate() to convert to a true date. But if there is no time element you will need to do the following.

@Date

If {yourdatefield} <> "-" then

date(tonumber(right({yourdatefield} ,4)), tonumber(mid({yourdatefield} ,4, 2)), tonumber(leftt({yourdatefield} ,2)))

Having converted to date you can use dateadd in your select statement

@Date < dateadd("yyyy", -5, currentdate)

This will be slow as Crystal will need to bring back all records to your report and perform this filter locally.

Ian

 

[tonymerseyfire]

Ian much appreciated that has worked perfectly.

Thanks again.