Date edit & formatting

Topic

New Reply

Access97 on Win98se. What is the best way to deal with displaying, maintaining and processing credit card expiration dates? Since a CC expiration date is in MM/YY format on the card I’m not sure how to proceed. I have a table with a credit card expiration date defined as a short date. To my knowledge that means that the field must contain a valid 6 digit date. I want to use a form that will only accept input, and output display of the month/year portion of the date. I also need to retrieve the 6 digit date and re-format it so that it only shows as MM/YY. Also since this is a true date I want to store the date in the table with the day portion of the field always containing a value of ’01’, so that a credit card that expires on 10/01 would really show as 10/01/01 in the table and when the record is read from the table for display on the form it will appear as 10/01. Thanks in advance.

Reply | Quote

Replies

I’ll leave it to others to show you the input masks/formats/whatever. However, you said you wanted the day to be stored as 01. The credit cards that I’ve run into expire on the last day of their expiry month, not the first day. So, if you are doing date comparisons to check whether a card is valid, you’ll want to either store the last day of the month or the first day of the next month.

Reply | Quote

Dear Gerald,

You can use “Datepart” to extract only the month part and the year part from the current date value, then check these against the cards expiry date. I think you could possibly store the expiry date, not in a date field, but as two separate text fields, month and year.

Reply | Quote

Thanks all. My intention was to use true date data types (in the hope that date data types would support month, day and year values substringing functions), but alas that was not to be. I instead ended up redefining the field in the table as text and doing the old fashioned cobol style edits to ensure the month and year are valid and that they are not ‘old’ values when compared to the current month & year. Problem solved. Thanks to all for your suggestions.

Reply | Quote

My version of Access supports all of those:

Day(date)
Month(date)
Year(date)

Those will give you numeric values, but if you really want a string, there are several functions to convert it depending on how you want it formatted. However, I would think that what you really want is to store the expiration date as a date value of the last day of the month specified and then you can compare to see if today’s date is greater than the exipration date. If dteExpDate contains the date value of the first of the month, you can use the following function to get the date value of the last day of the month:

DateSerial(Year(dteExpDate),Month(dteExpDate)+1,1)-1

Reply | Quote