Within one year from now

[technonurse]

I need to compare a specific date field with exactly one year from that date. My formula needs to populate a column with an "X" if that specific date ages more than one year.

E.G. A review was conducted on 10-3-02, so when 10-3-03 comes around, the "X" needs to populate the column. Technonurse-Spokane

 

[rosemaryl]

Dear Technonurse:

The following should work:

if currentdate >= Dateadd("yyyy",1,{table.ReviewDate}}
then "X" else ""

Hope that helps,

ro
Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[technonurse]

The above-formula I believe, is close. However, Crystal is asking for a literal date and won't accept the table date. Technonurse-Spokane

 

[rosemaryl]

Dear Technonurse,

Two things,

I noticed that my typed formula above contains a typo (sorry).

It should be:

if currentdate >= Dateadd("yyyy",1,{table.ReviewDate})
then "X" else ""

If you typed it correctly as above and the error is "a date is required here" then your date field is not a true date field, but instead a text field.

Try

if currentdate >= Dateadd("yyyy",1,Date({table.ReviewDate}))
then "X" else ""

When you right click on the field and browse, what is the field type?

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[technonurse]

Hi Rosemary: Thanks for your help. I placed your second formula into my report and got no errors. I went to test it placing a > year old date in my field and got no "X." When I browse on the field, it is a length-10 string. I'm encouraged that I get no error readings, just wondering why I got no "X" in the column.

Technonurse Technonurse-Spokane

 

[rosemaryl]

Dear Technonurlse,

Please post the exact format of the date string so that I can look at the format.

Is the format 02/02/2002 or 2002/02/02?

Please post examples of valid dates, your select statement, if any, etc.

I need to see data.

Are you sure that you have review dates that are greater than or equal to today? (the review date was one year ago).

ro
Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[technonurse]

Hi Rosemary: I was able to test the field using a different table that had a lot of old data in it and the formula worked great. I hadn't tested it thoroughly enough before my last communication. Thanks a million!

Technonurse Technonurse-Spokane

 

[rosemaryl]

Dear Technonurse,

Happy to help,

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.