Formating a field for expiry date ?

[malagasy]

Hi,

I am trying to create some kind of a automated formatting for one of the field in my customer database.

In this case, I have a field with the expiry date of their credit card. At the moment, this field is a Text field to allow me to enter the expiry date in such way >>> 07/07 or 03/05 , etc...

I'd like to find a way for that field in my layout to become red (wether the whole field or only the font) when the expiry date is less than the month and year of the current date...

If this is not clear, here is an exemple > We are in January 2007.

Customer A has a credit card expiring 05/07 so it's cool.

Customer B has a credit card expiring 12/06 so it should become red.

Customer C has a CC expiring 01/07 so it should become red.

How to do

Thanks for your help.

[Lee Smith]

Do you want the current Field to change color, or do you want a different field to reflect a flag.

Lee

[malagasy]

the Expiry date field preferably

[Lee Smith]

You can use an Auto Enter Calculation for the field. Replace [color:blue]Expiration used with the Field Name of [color:blue]your Expiry field

Let (

ExCalc = GetAsDate ( Left ([color:blue]Expiration;2) & "/" & "01" & "/" & Right ( [color:blue]Expiration; 2)) - 30 ≤ Get(CurrentDate) ;

Case ( ExCalc = 1 ; TextColor ( [color:blue]Expiration ; RGB ( 500 ; 0 ; 0 ) ) ; TextColor ( [color:blue]Expiration ; RGB ( 0 ; 0 ; 0 ) )))

Be sure to [color:blue]DeSelect the checkbox for [color:red]Do not replace existing value in the field (if any)

I tested this using the examples you provided. However, be sure and take a look at the ones that have passed, and let me know if there is a problem.

Lee

[malagasy]

Thanks,

Will integrate it and keep you updated.

[malagasy]

Alright,

I have replaced the required field and copy and paste the function but it doesn't seem to be working...

Let ( ExCalc = GetAsDate ( Left (ExpiryDate;2) & "/" & "01" & "/" & Right ( ExpiryDate; 2)) - 30 ≤ Get(CurrentDate) ;

Case ( ExCalc = 1 ; TextColor ( ExpiryDate ; RGB ( 500 ; 0 ; 0 ) ) ; TextColor ( ExpiryDate ; RGB ( 0 ; 0 ; 0 ) )))

What am i doing wrong ?

Indeed, all the date get red now...even the one which haven't expired.

If this can help, my field is a text field and the format of the date entered is mm/yy

[Lee Smith]

I pasted your calculation into my test file, and it is working properly.

can you post a screen shot of the results, or a copy of your file.

Lee

[comment]

What is the date format of your system?