Need Help With a Date Based Expression

[FSNut01]

I am creating ID cards using a report based on an active link table in a MySQL DB. I need to show an expiration date on the cards and it will always be April 30th of the next April.

What I need to do is calculate the next April 30th. In other words I need some kind of conditional statement that says if it's between May 1st and Dec. 31st it should be April 30, "current Year + 1, if it's between Jan 1st and April 30 it should be April 30, current year.

Can someone give me an idea of how to do this? I have tried several methods and none give me what I need.

Thanks.

[aschone]

Re: Need Help With a Date Based Expression

DIM vDateD as D
vDateD = {05/01/2010}

?IF(vDateD <= convert_type("{04/30/"+year(vDateD)+"}","date"),"{04/30/"+year(vDateD)+"}","{04/30/" + (year(vDateD)+1) +"}")
= "{04/30/2011}"


vDateD = {04/01/2010}

?IF(vDateD <= convert_type("{04/30/"+year(vDateD)+"}","date"),"{04/30/"+year(vDateD)+"}","{04/30/" + (year(vDateD)+1) +"}")
= "{04/30/2010}"

[G Gabriel]

Re: Need Help With a Date Based Expression

Just a quick-one (at the brain-stem level i.e., not much thought put into it):

dim x as d={01/01/2010}
?date_value(if(month(x)<5,year(x),year(x)+1),4,30)
= {04/30/2010}
x={05/01/2010}
?date_value(if(month(x)<5,year(x),year(x)+1),4,30)
= {04/30/2011}

When my cerebrum is back at work, I might come up with something else.

[G Gabriel]

Re: Need Help With a Date Based Expression

With the cerebellum awakening, if the date is:
01/01/1999
What date do you want? April 30 of 2010? or 1999?
And what if the date:
11/1/1999 ?

[G Gabriel]

Re: Need Help With a Date Based Expression

I'll answer my own questions before I drift into comma:

delete x
dim x as d={01/01/2010}
?if(x<date_value(year(date()),4,30),date_value(year(date()),4,30),date_value(year(x)+1,4,30))
= {04/30/2010}
x={01/01/1999}
?if(x<date_value(year(date()),4,30),date_value(year(date()),4,30),date_value(year(x)+1,4,30))
= {04/30/2010}

[FSNut01]

Re: Need Help With a Date Based Expression

With the cerebellum awakening, if the date is:
01/01/1999
What date do you want? April 30 of 2010? or 1999?
And what if the date:
11/1/1999 ?

The date would never be in the past.

This would not be a stored date. It would only be used at runtime for printing the current ID card or membership card. If the user prints a card between May 1 and Dec 31 of this year (2010) it should print April 30, 2011 and likewise between Jan 1 and April 30 of 2011. After May 1 2011, it should print April 30, 2012, etc..

[G Gabriel]

Re: Need Help With a Date Based Expression

I had assumed, by way of extrapolation, that the date is either current or future.
Either one of the above suggestions should work.

[MikeC]

Re: Need Help With a Date Based Expression

Doug,
Do you need this as a single line expression or can it be a if--then or select--case ?? This should always be stated---otherwise, the first will work regardless of context, but the other two will not say in the expression builder and such.

Maybe

if(between(date(),ctod("05/01/"+str(year(date()))),ctod("04/30/"+str(year(date())+1))),ctod("04/30/"+str(year(date())+1)),ctod("04/30/"+str(year(date())+2)))

[FSNut01]

Re: Need Help With a Date Based Expression

I think it could be either. I want to use it in a report as a calculated field.

[MikeC]

Re: Need Help With a Date Based Expression

A single line as I gave would be what is needed then....did you try my guess?

[FSNut01]

Re: Need Help With a Date Based Expression

Yes Mike, it seemed to work great as I tried various dates until I tried Mar 2, 2012 and it came up with April 30, 2014 instead of 2013. I'll take a look at it closer tomorrow to see if I can tell why. It has to be the +2 part kicking in.

Your suggestion seems to have pointed me in the right direction. Thank You.

[MikeC]

Re: Need Help With a Date Based Expression

Doug,
The function I gave assumed that you would be dealing with current dates.....if only the month and day are to be considered and the date you are testing is to be placed into the function as an argument, then that changes the function somewhat (although it would be similar).

[FSNut01]

Re: Need Help With a Date Based Expression

Mike, it should be based on the current system date. I experimented with changing the system date and running the report to see what it would print.