Credit Card Expiry Date MM/YY

[Bob Scarborough]

Hi,
I'm based on Australia so default date format on the PC is "DDMMYY"
I want to record credit card expiry date for a customers credit card transaction in MM/YY format.

Can this be done using a Date/Time field or do I need to use a text field? I have tried making the input mask MM/YY which appears ok. But if I enter 11/04 for this year the expiry date field contains 11-April-04 or 11-04-04. In other words although input mask is for Month and Year result in the field is Day and Month. What am I doing wrong?

Also I wanted to compare the expiry date on the card with the current month and year. By using the Month(Date()) I get "4" (As it is now April) and using Year(Date()) I get "4" ideally I would like to see "04" for Month and "04" for the year and concatenate same to achieve "04/04" to compare against expiry date on the card. How can I force the leading characters on both of the above functions to stop truncating leading Zeros?

If anyone esle has a better solution for resolving this issue or can post a sample solution it would be definitely appreciated.

Thanks in advance for any ideas or suggestions.

 

[dcx693]

I've never done this in a database, but in theory at least, I'd record the expiration date as a text string and use and input mask and some code to validate it. (I have seen some credit cards that have not just the month and year, but the date as part of the expiration date, but that's the exception as far as I know.)

If you had the expiration stored as something like 05/04 in text format, you can compare it against the current MM/YY by using this expression: Format(Date(),"MM/YY")

 

[Bob Scarborough]

DCX693: Thanks mate for the suggestion. I'll give it a whirl.

I hope weather is warming up in Brooklyn for the Easter Break. It was bonza here in Adelaide.

 

[Bob Scarborough]

Pat,

Many thanks!!

Bob.

 

[MyTech]

Here's your best solution (I think ).

That's what I did, while my system is set to USA date format (mm/dd/yyyy).


1. Field Type: "Date/Time".
2. I set Field Name as: "Exp".

In form:
3. Format>format: "mm/yy".

4. Create following 'After Update' macro on that same control ("Exp").

Event>After Update>Macro Builder:

SetTempVar: OD, Day([exp])
SetTempVar: OM, Month([exp])
SetValue: CDate([TempVars]![om] & "/" & ([TempVars]![od]+2000))
RemoveAllTempVars

('OD' stands for 'Old Day' and 'OM' stands for 'Old Month')
​

Now I just enter the month & year (let's say "6 13") - the system recods "6" as "Month" and "13" as "Day" and adds current year as "Year".
Then - my macro converts it to read the "Day" as "Year".

No need for input mask

 

[vbaInet]

I think between 2004 (the year the OP last posted) and now, the poster certainly would have moved on from this and probably have become an expert coder by now too

 

[MyTech]

You're right, vbaInet.

But I had in mind people just like me.

After searching and not finding the right answer that fits my needs, I worked a long time till I came up with the solution.

So why not share it with future searchers?
We're here to help others too, right?

 

[vbaInet]

Absolutely MyTech. Always good to share your findings.