Credit Card Expiration Date format MM/YY

[Kime1266]

Thank you in advance for any input/assistance. This is driving me crazy. I am storing my expiry date as date/time, format mm/yy, no input mask in my table. I have a form created from it & when I test the field it still shows up as mm/dd/yyyy. I also have the format of the field on the form as mm/yy but still mm/dd/yyyy. What am I missing or doing wrong?

Totally frustrated Kime

[NeoPa]

Hi Kime.

You need to understand what storing as a Date/Time means. Separately, that a format is just a format.

Todaye is 26 July 2018 here. Stored as a Date/Time this is a value. Displayed as mm/yy will show simply as 07/18. However - there are many other different dates that also show up as 07/18 when displayed that way. That doesn't mean they're also the same Date/Time value.

If you enter a value such as July 2018, or any valid variant of the same month, you will typically have a Date/Time returned that's the first day of that month - so, 1st July 2018. This can be displayed many ways but only when displayed as mm/yy will it show as 07/18.

I'm a little confused about what you describe as a field on your Form as Forms, while they have a Fields collection, only show those Fields via Controls. I would guess you're talking about a TextBox Control. I'd be interested to see a TextBox control that showed a Date/Time value as anything but mm/yy if it's been correctly set as having that format.

[Kime1266]

Thank you for your response, NeoPa. This was my first time posting so I may have been unclear. I have a field that I want the data to be stored/displayed in mm/yy format. I have the format property in the table as mm/yy and tried it with & without an input mask but when I enter test data in the field, it is returning a results exactly as you stated, in mm/dd/yyyy. Is there a way to have it display & store as I want?

[zmbd]

Kime1266:

If you a table such that:
[t_CreditCardInfor]
[PK] primary key as autonumber
[ExpDate] as date/time data type
[etc]

As NeoPa tried to explain to you - what you are asking doesn't make sense if you are using a date/time data-type.

If you enter a date such as
[t_CreditCardInfor].[ExpDate] = 01/18
expecting access to store that as MM/YY you will be disappointed. MM/YY - isn't a recognized valid date format for the date/time data type - period.

Access will convert the date to USA #01/18/1818# or some other date not MM/YY - there may be some locality applied depending on how you enter the date; however the effect will be the same - something random (if you want proof I'll attach some screen shots).

If you want to actually store the data as "MM/YY" then you will have to set the field to data type "Short Text" and use some magic vba or macro (these are not the same thing) to ensure that only numeric and the "/" are being entered.

Displaying the data as "MM/YY" from a date/time field is trivial - that's the format.

I'd suggest taking the user input and running a before_update event to convert an entry such as "12/18" to the internal date literal #12/01/2018# and store the value as a proper date/time value and using the formatting options of the table, query, or form control to display the information in the desired format.

[Kime1266]

That concept worked. Thank you so much.

[NeoPa]

ZMBD:
Access will convert the date to USA #01/18/1818# or some other date not MM/YY

To be technically accurate Access converts the input into a Date/Time value. Such a value doesn't have any format. It is a storage thing rather than a display thing.

Many people struggle to understand the difference and get all caught up because they assume one is the same as the other. I don't like to get into the nitty-gritty of Date/Time storage too much because it should be irrelevant how any particular engine chooses to handle things internally, but for Access it basically stores all dates as a signed floating-point value reflecting the number of days (including fractions thereof) since 30th December 1899. The possible formats for Date/Times are numerous - but irrelevant to how the data is stored. Without any format Now() (27/07/2018 15:37:08) would simply show as 43308.650787037, which gives a clearer understanding of the difference between Date/Time values and formatted Date/Time strings.

PS. Well done for explaining the situation as well as you did. The OP seems to have grasped at least some of the issue.
PPS. I'm sure you already understood everything exactly - just let slip a little in the explanation thereof ;-) I didn't mean to sound critical at all. Just very fussy about details.

[zmbd]

To be technically accurate Access converts the input into a Date/Time value. Such a value doesn't have any format. It is a storage thing rather than a display thing.

NeoPa,

You are absolutely correct -
In my defense ( :) ) I was trying to simplify the explanation by using the date literal (#DD/MM/YYYY#). I also wanted to point out that internally Access will tend interpret dates in the USA format (how many times has this tripped up a VBA script or a simple query?!)... unfortunately, we can't easily convert in our heads (27/07/2018 15:37:08) ==> #7/24/2018 15:37:08# ==> even though in the field it's actually stored as 43308.650787037

Would be nice if humans had a watch to tell time in the decimal format - then we'd argue about what start date we should use... Jan 1, 1900 or Dec 31, 1899 (Lotus eat your heart out)

[NeoPa]

Zmbd:
then we'd argue about what start date we should use... Jan 1, 1900 or Dec 31, 1899 (Lotus eat your heart out)

For reasons I've never really understood Access uses Dec 30 1899 (as per my post #6). Go figure.

?Format(0,"d/m/yyyy");
30/12/1899

[Kime1266]

How interesting. I was unaware that Access used Dec 30 1899. I thought ALL programs used 1/1/1900 as day 1. Thanks so much as I was able to get it to work using text as a result of both responses and learned a thing or two. On to my next posting question...

[NeoPa]

Coincidentally, and not by my looking but a good friend just explained it elsewhere out of the blue, I recently learned why Access uses 30th December 1899, rather than 31st December 1899, as day 0.

It turns out that Lotus 123, which many of you will remember as the de-facto leader in spreadsheet software before MS came along with Excel and eventually blew it out of the water, had an error in its date logic whereby it treated February 1900 as having 29 days instead of 28 (Divisble by 4; divisible by 100; not divisible by 400). This same error was propagated by Excel as, at the time, it was important for it to behave like the market leader so it could claim compatibility and encourage users to switch without strange problems. Thus it is that even now, if you show Format(60,"d mmm yyyy") you'll see 29 Feb 1900.

It is because of this that the base date, which should have been 31 Dec 1899, turned into 30 Dec 1899. Who'd 'a' thunk?