By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,351 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

MS Access Date Input Issue

P: 6
Ok, problem with dates. Doing a computer inventory form, one field of which is for tracking warranty end dates. Now, displaying the date as mmm-yy is easy, i got that one. But i need a way to be able to input the date as mmm-yy or mm-yy (either or both is fine). Currently Access is treating the "yy" portion of the input as the day and appending the current year, rendering my input of "Mar-08" as "Mar-09" instead. How do i force access to either ignore the day portion of the field when input or append a specific day (01) into the date?

--dunerat
Jan 31 '09 #1
Share this Question
Share on Google+
4 Replies


100+
P: 675
In order to store a date, Access needs a complete date, a unique day in time. Year can default to the current year, but should Mar-08 default to todays "day", the first of March, or?
Try dteInputDate = "#1 " & txtYourFormTextbox & "#" where dteInputDate is your date variable.

OldBirdman
Jan 31 '09 #2

P: 6
Well, what actual day of the month Access records is irrelevant, since it's only being tracked by month. It would be convenient, i suppose to have them all be the same day, but it doesn't matter.

Would it be better to simply put in the day in the table and just format the forms to display without it? i'm not sure which the better practice would be: recording the extraneous data and formatting it out of the display, or recording only the necessary data and displaying what there is.

--dunerat
Feb 1 '09 #3

Expert Mod 2.5K+
P: 2,545
Hi Dunerat. Oldbirdman's answer is right - to store a date value Access does need a day number. Dates are stored internally as whole numbers representing the number of days elapsed since 1 Jan 1900. You are right that you can format the date to ignore the day value and just display the month and year - but internally such a date must still be stored as a particular day in the month. Luckily, Access will add a default day value of the first of the month if a custom format is set on the entry control to enter the month and year only, as long as the year is entered in full - not as a two-digit abbreviation.

In the Format property of the textbox you are using to enter the date value enter the following custom format, without quotes:

mm-yyyy

You can then enter a date directly without entering the day (e.g. as 2/2009 - Access will default the date to the first of the month automatically.

This solution has been tested with Access 2003.

If you must abbreviate the year you will not be able to use this solution for date entry, as Access does indeed try to interpret what you enter to the best of its ability - and it gets itself confused into interpreting the values entered as day and month instead of month and year.

You could use a custom unbound textbox to enter date values as month and year values, making use of the After Update event to form a true date from these using the DateSerial function to do so, but this is adding considerable complication to your form (you could not easily use the same textbox to display an existing date value, for instance) and I do not think this is worth the effort for the sake of avoiding entering a full year number...

-Stewart
Feb 1 '09 #4

P: 6
Entering a full year is not a real problem, the users will just have to deal with that. That should solve this one. Thanks both of you.

--dunerat
Feb 1 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.