Date Auto Changes the Year When Entering????

[Njliven]

I have a form in Access where a report date is to be entered. We only want to show a date format like Dec-13. Month-Year. I set a input mask to keep this consistent when others are entering.
Now it is January and people are trying to enter their reports for December, when they put in Dec-13. It automatically changes to Dec-14. WHY!!!!
I tried to go to the table and change the date to the year 13, but it just keeps changing back to 14. Help Please. This has me completely baffled.
Thanks!

[ItsMe]

Do you have a default value set for the date field in your table? It does not make sense when you say, "...but it just keeps changing back." Are you referring to when a new record is created the date field is populated with the year 2014?

[JoeM]

I think here is what is going on.

You can display dates any way you want to, but when you enter them you need to enter a full date (month, day, and year). You can actually leave the year off, and it defaults to the current year.
So, if you enter Dec-13, what you are really entering is the date "the 13th day of December". Leaving off the year, it assumes 2014.
So, since it is displaying "mmm-yy", it is displaying "Dec-14".

So the mistake is in entering "Dec-13", you think you are entering the "month and year", but really you are entering "month and day".
Regardless of how it is displayed, try entering a full date (year included), and you will get the results you want.

[JoeM]

In a nutshell, Format only controls the View of how the data is presented, it does NOT affect data entry.

[Njliven]

I think you may be right here, but the mask only lets you enter the 3 letter month and the 2 digit (year) only I think it is the day not the year, but the year is displayed. Is does not sound like there is a way to enter the date without a day, which is what I want. May need to rethink this field and make it a text field with the input mask instead.

I think here is what is going on.

You can display dates any way you want to, but when you enter them you need to enter a full date (month, day, and year). You can actually leave the year off, and it defaults to the current year.
So, if you enter Dec-13, what you are really entering is the date "the 13th day of December". Leaving off the year, it assumes 2014.
So, since it is displaying "mmm-yy", it is displaying "Dec-14".

So the mistake is in entering "Dec-13", you think you are entering the "month and year", but really you are entering "month and day".
Regardless of how it is displayed, try entering a full date (year included), and you will get the results you want.

[JoeM]

It is important to understand how dates are stored in Access and Excel. Dates are actually stored as numbers, specifically the number of days since January 1, 1900.
So whenever you enter any date in Access, in order to store it, it actually needs to know which DAY the date is, so it can store the correct number which represents the day.
Access is "smart" enough if you leave the year off, it defaults/assumes the current year (a little shortcut, if you will). But it makes no such assumptions about DAY, you need to enter a day.
If you just wanted to store YEAR and DAY, you would have to make it a Text (String) Datatype instead of a Date Datatype. Of course, then since it is stored as Text and not a Date, you lose all sort of Date functionality (calculations, sorting, etc).

Whenever I have a situation like this, I just enter the 1st day of the month.

If you want to get really clever, you could enter the date like you have been as Text in a TextBox, and have VBA code run behind the scenes and convert it to a date (the first day of the month), and store that.

[Njliven]

So, if I make my field a text field and use an input mask to display it with the MMM/YY. Can you provide a sample of the code that would convert it and store it as a date, and where would this code be used? It is important to keep this as a date so when we run reports it put the records in month order. Thanks!

[ItsMe]

When I want to store a time value created by user input, I use two fields. Both fields as Date/Time data types and I am sure to format the fields at the table level. One field format as Short Date and the other formated as Medium Time. I use the date picker for the date and I do not bother with input masks. If you do not want the user to input the date field you can populate programticaly. Perhaps by using a default value of Date().

[Njliven]

JoeM
So are you telling me that I can have a text field with an input mask to hold text like "Dec-13", Jan-14, etc. I would still like the Dec-13 to display in the form. The table can store the date with the first day of the month. Can you help me with the code for that and where to place the code (I would think "on exit"). Thanks!

Thanks!

[amrut]

One more way is to use two combo boxes one for the months ( Value list like 1;Jan;2;Feb etc,2 columns, hide first column) and other for years and a bound textbox ( hidden, default value date based on two combo boxes) for your date field . In the after update event of both of them or an event suitable in your case, change the value of this text box to date format using the values in the two combo boxes to save in table as date.
Does this helps ?

[JoeM]

Sorry, I was not on-line at all yesterday. ItsMe and amrut have some very good suggestions, using one of those would be the recommended way to go. Either:

1. Use a date-picker calendar. This way, they do not need to type in anything, just click the day they want. You can use the Format property to just display the month and year with you like.
If there is concern that they may pick different days during the month, which could mess up your sorting, you can use an AfterUpdate event on the date field to automatically change the date picked to the first day of the month (so it is the same for all dates).

2. I have used concepts like amrut's before. Use combo boxes or radio buttons where they pick each piece of the date (month and year), and then have VBA code build and store the date in your field.

Both of these ideas are preferable to the first suggestion I gave. The main difference is using one of these options, you control the values that they can pick from. If we allow them to type a date in a Text Box, they could have a typo which could cause errors (i.e. Jab-14). You could add error-handling code to the VBA code to handle it, but it is easier to do it one of the other two ways.

[Njliven]

JoeM,
Option 1 sounds like the best option for my situation. Can you help with the code for converting the day to the first in case they put in something other than the 1st. Thanks!

[JoeM]

Are you using this to only add new records, or also adjust/update existing records?

[Njliven]

For new records, but it would be nice if it would change any existing records also, otherwise I will have to do it manually.

[JoeM]

OK, making sure that your Form is bound to your data source (whether that be a Table or Query), add your Date field to the Form. In this example, let's say it is named "MyDate".
Then go to the Properties of this field, go to the "Event" tab, and go to the "After Update" property and add the following VBA code on that action (modify field name, as needed):

Private Sub MyDate_AfterUpdate()
    
'   If a non-zero date is entered, change date to first day of the month
    If Me.MyDate > 0 Then
        Me.MyDate = DateSerial(Year(Me.MyDate), Month(Me.MyDate), 1)
    End If
    
End Sub

So, it should automatically update any date they select back to the first day of that month. You can also enter "mmm-yy" in the Format property of this field, if you like.

Note, that the user can still type in the date manually. So you may want to add a note on the sheet instructing them to use the Date Picker instead of typing it in manually.