Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86

    Date Auto Changes the Year When Entering????

    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!

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    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?

  3. #3
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    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.

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    In a nutshell, Format only controls the View of how the data is presented, it does NOT affect data entry.

  5. #5
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86
    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.


    Quote Originally Posted by JoeM View Post
    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.

  6. #6
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    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.

  7. #7
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86
    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!

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    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().

  9. #9
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86
    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!

  10. #10
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    India
    Posts
    616
    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 ?

  11. #11
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    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.

  12. #12
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86
    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!

  13. #13
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Are you using this to only add new records, or also adjust/update existing records?

  14. #14
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86
    For new records, but it would be nice if it would change any existing records also, otherwise I will have to do it manually.

  15. #15
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    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):
    Code:
    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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 01-09-2013, 11:16 AM
  2. Replies: 4
    Last Post: 01-10-2012, 06:26 PM
  3. Auto fill Current year
    By Bhat59 in forum Forms
    Replies: 3
    Last Post: 10-17-2011, 08:56 PM
  4. Replies: 1
    Last Post: 08-17-2011, 11:13 AM
  5. Replies: 1
    Last Post: 12-09-2005, 10:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums