Date Expressions

[David Darby]

I want to filter all records to print the one expiring next month. An expression of current month plus 1 return all records expiring in the next month, regardless of year. What expression do I use to filter JUST those expiring the next month. Today is September 27, 2002. I want only those records that expire in October 2002, not 2003, 2004, etc.
Thanks.

[Jay Talbott]

RE: Date Expressions

David,

The interactive windows gives the following results:

a={10/05/2002}

b={10/05/2003}

y=addmonths(date()-day(date())+1,1)

z=addmonths(date()-day(date()),2)

?between(a,y,z)
= .T.

?between(b,y,z)
= .F.

y and z are formulae (formulas?, formulii? .. anyway) that define the first day of next month and the last day of next month.

Thsi should get you started. If you need further help, let us know.

Regards,

Jay Talbott

[David Darby]

RE: Date Expressions

James:

I'm still having problems. My accounts expire monthly, for up to 60 months.

I would like to print a letter to all accounts that will expire the following month. Is there a Query or filter to use in selecting all those accounts then printing the accounts selected?

Thanks.

[Tom Cone Jr]

RE: Date Expressions

David,

if the expiration date is stored in a date field called EXPIRE_DATE, then something like this could be used as the filter expression in a query, to get the records which have EXPIRE_DATE values that will occur next month.

"cmonth(expire_date) + cyear(expire_date) = cmonth(addmonths(date(),1) + cyear(addmonths(date(),1)"

Example:

if today is 10/05/2002

cmonth(date()) + cyear(date()) would be October2002

cmonth(addmonths(date(),1) + cyear(addmonths(date(),1) would be November2002

The filter expression works by comparing the month and year components of each expire_date field against the month and year values of the next calendar month.

hope this helps.

-- tom

[David Darby]

RE: Date Expressions

Tom:

Looks like this might work. Thanks for the help.